Stored Procedures

1. StoredProcedures A stored procedure is a named collection of SQL statements language. You can create stored procedures for commonly usedfunctions and to increase performance. SQL Server also providessystem procedures to perform administrative tasks and to update thesystem tables.

2. How StoredProceduresWork When you run a stored procedure, Server prepares an executionplan so that the procedure’s execution is very fast. Stored procedures can: Take parameters Call other procedures Return a status value to a calling procedure or batch to indicatesuccess or failure and the reason for failure Return values of parameters to a calling procedure or batch Be executed on remote Servers

3. How StoredProceduresWork The ability to write stored procedures greatly enhances the power, efficiency,and flexibility of SQL. Compiled procedures dramatically improve theperformance of SQL statements and batches. In addition, stored procedures onother Servers can be executed if both your server and the remoteserver are set up to allow remote logins.

4. How StoredProceduresWork Stored procedures differ from ordinary SQL statements and from batches ofSQL statements in that they are precompiled. The first time you run aprocedure, Server’s query processor analyzes it and prepares anexecution plan that is ultimately stored in a system table. Subsequently, theprocedure is executed according to the stored plan. Since most of the queryprocessing work has already been performed, stored procedures execute almostinstantly.

5. Creating and Using Stored Procedures The syntax for creating a simple stored procedure, without special featuressuch as parameters, is: create procedure procedure_name as SQL_statements Sample: create procedure namelist as select name from sysusers Toexecutetheprocedure namelist execute namelist exec namelist

6. Creating and Using Stored Procedures To execute a stored procedure on a remote Server, you must give theserver name. The full syntax for a remote procedure call is: execute server_name.[database_name].[owner].procedure_name

7. Creating and Using Stored Procedures A procedure can include more than one statement. create procedure showall as select count(*) from sysusers select count(*) from sysobjects select count(*) from syscolumns When a create procedure command is successfully executed, the procedure’sname is stored in sysobjects, and its source text is stored in syscomments. You can display the source text of a procedure with sp_helptext: sp_helptextshowall

8. StoredProceduresandPerformance The queries used by stored procedures and triggers are optimized only when they are compiled. As indexes or other changes that affect statistics are made to the database, compiled stored procedures and triggers may lose efficiency. By recompiling stored procedures and triggers that act on a table, you can reoptimize the queries. sp_recompileshowall

9. Creating and Using Stored Procedures The complete syntax for create procedure is: create procedure [owner.]procedure_name[;number] [(]@parameter_name datatype [(length) | (precision [, scale ])] [= default] [output] [, @parameter_name datatype [(length) | (precision [, scale])] [= default] [output]]...[)] [with recompile] as {SQL_statements | external name dll_name} You can create a procedure in the current database only.

10. Creating and Using Stored Procedures Here is the complete syntax statement for execute: [exec[ute]] [@return_status = ] [[[server.]database.]owner.]procedure_name[;number] [[@parameter_name =] value | [@parameter_name =] @variable [output] [, [@parameter_name =] value | [@parameter_name =] @variable [output]...]] [with recompile]

11. Creating and Using Stored Procedures Example: Given an author’s last andfirst names, the procedure displays the names of any books written by thatperson and the name of each book’s publisher. create procau_info @lastnamevarchar(40), @firstnamevarchar(20) as select au_lname, au_fname, title, pub_name from authors, titles, publishers, titleauthor where au_fname = @firstname and au_lname = @lastname and authors.au_id = titleauthor.au_id and titles.title_id = titleauthor.title_id and titles.pub_id = publishers.pub_id Execution: au_info Ringer, Anne

12. Creating and Using Stored Procedures Example: The following stored procedure queries the system tables. Given a table nameas the parameter, the procedure displays the table name, index name, and indexID. create procshowind @table varchar(30) as select table_name = sysobjects.name, index_name = sysindexes.name, index_id = indid fromsysindexes, sysobjects where sysobjects.name = @table and sysobjects.id = sysindexes.id Execution: execute showind titles exec showind titles execute showind @table = titles execute GATEWAY.pubs2.dbo.showind titles showind titlesorexecuteshowindtitles

13. Creating and Using Stored Procedures If you supply the parameters in the form “@parameter = value” you can supplythem in any order. Otherwise, you must supply parameters in the order of theircreate procedure statement. If you supply one value in the form “@parameter= value”, then supply all subsequent parameters this way. Belowprocedure displays the datatype of the qty column from the salesdetailtable. create procedure showtype @tabnamevarchar(18),@colnamevarchar(18) as select syscolumns.name, syscolumns.length, systypes.namefrom syscolumns, systypes, sysobjects where sysobjects.id = syscolumns.idand @tabname = sysobjects.nameand @colname = syscolumns.nameand syscolumns.type = systypes.type Execution:execshowtype @colname = qty , @tabname = salesdetail

14. Creating and Using Stored Procedures You can assign a default value for the parameter in the create procedurestatement. This value, which can be any constant, is used as the argument to theprocedure if the user does not supply one. Here is a procedure that displays the names of all the authors who have written a book published by the publisher given as a parameter. If no publisher name is supplied, the procedure shows the authors published by AlgodataInfosystems. create procpubinfo @pubnamevarchar(40) = "AlgodataInfosystems" as select au_lname, au_fname, pub_name from authors a, publishers p, titles t, titleauthor ta where @pubname = p.pub_name and a.au_id = ta.au_id and t.title_id = ta.title_id and t.pub_id = p.pub_id exec pubinfo

15. Creating and Using Stored Procedures This procedure, showind2, assigns “titles” as the default value for the @tableparameter: create proc showind2 @table varchar(30) = titles as select table_name = sysobjects.name, index_name = sysindexes.name, index_id = indid from sysindexes, sysobjects where sysobjects.name = @table and sysobjects.id = sysindexes.id The column headings, for example, table_name, clarify the result display. Hereis what showind2 shows for the authors table: showind2 authors Showind2Server uses the default, titles

16. Creating and Using Stored Procedures In the create procedure statement, you can declare null as the default value forindividual parameters: create procedure showind3 @table varchar(30) = null as if @table is null print "Please give a table name." else select table_name = sysobjects.name, index_name = sysindexes.name, index_id = indid from sysindexes, sysobjects where sysobjects.name = @table and sysobjects.id = sysindexes.id The column headings, for example, table_name, clarify the result display. Hereis what showind2 shows for the authors table: showind3 authors Showind3

17. Creating and Using Stored Procedures Using more than one parameter: create proc au_info2 @lastnamevarchar(30) = "D%", @firstnamevarchar(18) = "%" as select au_lname, au_fname, title, pub_name from authors, titles, publishers, titleauthor where au_fname like @firstname and au_lname like @lastname and authors.au_id = titleauthor.au_id and titles.title_id = titleauthor.title_id and titles.pub_id = publishers.pub_id Execution: au_info2 au_info2 Ringer

18. Creating and Using Stored Procedures Procedure groups: The optional semicolon and integer number after the name of the procedure inthe create procedure and execute statements allow you to group procedures ofthe same name so that they can be dropped together with a single dropprocedure statement. Procedures used in the same application are often grouped this way. Forexample, you might create a series of procedures called orders;1, orders;2, andso on. The following statement would drop the entire group: drop proc orders Once procedures have been grouped by appending a semicolon and number totheir names, they cannot be dropped individually. For example, the followingstatement is not allowed: drop proc orders;2

19. Creating and Using Stored Procedures Nesting procedures within procedures: Nesting occurs when one stored procedure or trigger calls another. You can call another procedure by name or by a variable name in place of theactual procedure name. For example: create procedure test1 @proc_namevarchar(30) as exec @proc_name

20. Creating and Using Stored Procedures Using temporary tables in stored procedures: You can create and use temporary tables in a stored procedure, but thetemporary table exists only for the duration of the stored procedure that createsit.A single procedure can: • Create a temporary table • Insert, update, or delete data • Run queries on the temporary table • Call other procedures that reference

21. Creating and Using Stored Procedures Using temporary tables in stored procedures: Since the temporary table must exist to create procedures that reference it, here are the steps to follow: 1. Create the temporary table using a create table statement or a select intostatement. For example: create table #tempstores (stor_id char(4), amount money) 2. Create the procedures that access the temporary table (but not the one thatcreates it). create procedure inv_amounts as select stor_id, "Total Due" = sum(amount) from #tempstores group by stor_id 3. Drop the temporary table: drop table #tempstores 4. Create the procedure that creates the table and calls the procedures createdin step 2: create procedure inv_proc as create table #tempstores (stor_id char(4), amount money)

22. Creating and Using Stored Procedures Using temporary tables in stored procedures: 4. Create the procedure that creates the table and calls the procedures createdin step 2: create procedure inv_proc as create table #tempstores (stor_id char(4), amount money) insert #tempstores select sales.stor_id, sum(qty*(100-discount)/100*price) from sales, titles,discounts where sales.title_id = titles.title_id group by sales.stor_id, sales.title_id execinv_amounts When you run the inv_proc procedure, it creates the table, but it only existsduring the procedure’s execution. Try inserting values into the #tempstorestable or running the inv_amounts procedure: You cannot, because the #tempstores table no longer exists.

23. Returning information from stored procedures Stored procedures can return the following types of information: • Return status – indicates whether or not the stored procedure completedsuccessfully. •procrole function – checks whether the procedure was executed by a userwith sa_role, sso_role, or ss_oper privileges. • Return parameters – report the parameter values back to the caller, whocan then use conditional statements to check the returned value.

24. Returning information from stored procedures Return status: Stored procedures report a return status that indicates whether or not theycompleted successfully, and if they did not, the reasons for failure. This valuecan be stored in a variable when a procedure is called, and used in futureTransact-SQL statements. Here is an example of a batch that uses the form of the execute statement thatreturns the status: declare @status int execute @status = byroyalty 50 select @status

25. Returning information from stored procedures

26. Returning information from stored procedures User-generated return values: You can generate your own return values in stored procedures by adding aparameter to the return statement. You can use any integer outside the 0 through-99 range. The following example returns 1 when a book has a valid contractand returns 2 in all other cases: create proccheckcontract @titleidtid as if (select contract from authors wheretitle_id= @titleid) = 1 return 1 else return 2

27. Returning information from stored procedures The following stored procedure calls checkcontract, and uses conditional clauses to check the return status: create procget_au_stat @titleidtid as declare @retvalueint execute @retvalue = checkcontract @titleid if (@retvalue = 1) print "Contract is valid." else print "There is not a valid contract." Here are the results when you execute get_au_stat with the title_id of a bookwith a valid contract: get_au_stat MC2222 Contract is valid

28. Returning information from stored procedures Return parameters: Another way that stored procedures can return information to the caller isthrough return parameters. The caller can then use conditional statements to check the returned value. This stored procedure performs multiplication on two integers (the third integer, @result, is defined as an output parameter): create procedure mathtutor @mult1 int, @mult2 int, @result int output as select @result = @mult1 * @mult2 To use mathtutorto figure a multiplication problem, you must declare the@result variable and include it in the execute statement. Adding the outputkeyword to the execute statement displays the value of the return parameters. declare @result int exec mathtutor 5, 6, @result output select @result

29. Returning information from stored procedures Return parameters: This stored procedure checks to determine whether new book sales wouldcause an author’s royalty percentage to change (the @pc parameter is definedas an output parameter): create procroy_check @title tid, @newsalesint,@pc int output as declare @newtotalint select @newtotal = (select titles.ytd_sales +@newsales from titles where title_id = @title) select @pc = royalty from roysched where @newtotal >= roysched.lorangeand@newtotal< roysched.hirangeand roysched.title_id = @title

30. Returning information from stored procedures Return parameters: The following SQL batch calls the roy_check after assigning a value to thepercent variable. The return parameters are printed before the next statement inthe batch is executed: declare @percent int select @percent = 10 execute roy_check "BU1032", 1050, @pc = @percent output select Percent = @percent

31. Returning information from stored procedures Return parameters: The following stored procedure calls roy_check and uses the return value forpercent in a conditional clause: create procnewsales @title tid, @newsalesint as declare @percentint declare @stor_pcint select @percent = (select royalty from roysched, titles whereroysched.title_id = @title andytd_sales>= roysched.lorange andytd_sales < roysched.hirange androysched.title_id = titles.title_id) select @stor_pc = @percent execute roy_check @title, @newsales, @pc = @percent output if @stor_pc!= @percent begin print "Royalty is changed." select@Percent= @percent end else print ‘Royalty is the same.’