Creating And Managing Stored Procedure In SQL Server 2008

What is a Stored Procedure


They are one or more SQL programs stored in a database as an executable object. They can be called interactively, from within a client application or from another stored procedure and from within triggers. We can pass parameters to and return from stored procedures to increase their usefulness and flexibility. A stored procedure can return a number or result set and a status code.

Advantage of using Stored Procedure
  1. Modular Programming: Stored procedures are modular. This is a good thing from a maintenance standpoint. When query trouble arises in your application, you would likely agree that it is much easier to troubleshoot a stored procedure than an embedded query buried within many lines of GUI code.
     
  2. Function based access to tables: A user can have permissions to execute a stored procedure without having permission to operate directly on the underlying tables.
     
  3. Reduced network traffic: Stored procedures can consist of many individual SQL queries but can be executed with a single statement. This allows us to reduce the number and size of calls from the client to server.
     
  4. Faster Execution: SP query plan are kept in memory after the first execution. The code doesn't have to be reparsed and reoptimized on subsequent executions.

Disadvantage of using Stored Procedures

  1. Increase in server processing requirement: Using stored procedures can increase the amount of server processing. In a large user environment with considerable activity in the server, it may be more desirable to offload some of the processing to the client side.
     
  2. Business Logic in SP: Do not put all of your business logic into stored procedures. Maintenance and the agility of your application becomes an issue when you must modify business logic in T-SQL. For example, ISV applications that support multiple RDBMS should not need to maintain separate stored procedures for each system. 

Big Question. When to use Stored Procedures


Stored procedures are well suited for 2-tier environment, but the trend is shifting to 3-tier n more environments. In such scenario business logic is often handled in some middle tier. So in such scenarios, we would like to restrict the stored procedures to performing basic data-related tasks, such as SELECT, UPDATE, DELETE.

For all examples shared below I have used Pubs database. You can download its MSI file from here and then attach .mdf file in your SQL Sever 2008.

http://www.microsoft.com/downloads/en/details.aspx?FamilyId=06616212-0356-46A0-8DA2-EEBC53A68034&displaylang=en

Creating Stored Procedure


We need to give the procedure a unique name within the schema and then write the sequence of SQL statements to be executed within the procedure. Following is the basic syntax for creating stored procedures:

Expand database->Select Pubs database->Select Programmatically->Select Stored Procedure->right click and select New Stored Procedure. SQL Server opens a new query window with a default stored procedure creation template like below.
  1. -- ================================================  
  2. SET ANSI_NULLS ON  
  3. GO  
  4. SET QUOTED_IDENTIFIER ON  
  5. GO  
  6. -- =============================================  
  7. -- Author:        <Author,,Name>  
  8. -- Create date: <Create Date,,>  
  9. -- Description:   <Description,,>  
  10. -- =============================================  
  11. CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName>   
  12.       -- Add the parameters for the stored procedure here  
  13.       <@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>,   
  14.       <@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>  
  15. AS  
  16. BEGIN  
  17.       -- SET NOCOUNT ON added to prevent extra result sets from  
  18.       -- interfering with SELECT statements.  
  19.       SET NOCOUNT ON;  
  20.   
  21.     -- Insert statements for procedure here  
  22.       SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>  
  23. END  
  24. GO  

Let us understand this template.

This template contains parameters for parameter names, procedure name, author name, create date, and so on. These template parameters are in the format <parameter, type, value>:

  1. PARAMETER_NAME: It is the name of the template parameter in the script.

  2. DATA_TYPE: It is the optional data type of the template parameter.

  3. VALUE: It is the default value to be used to replace every occurrence of the template parameter in the script.

  4. SET NOCOUNT ON,

    1) It gives performance. When SET NOCOUNT is ON, the count (indicating the number of rows affected by a Transact-SQL statement) is not returned.
    2) When SET NOCOUNT is OFF, the count is returned. It eliminates the sending of ONE_IN_PROC messages to the client for each statement in a stored procedure.
    3) For stored procedures that contain several statements that do not return much actual data, this can provide a significant performance boost because network traffic is greatly reduced. The setting of SET NOCOUNT is set at execute or run time and not at parse time.

  5. RETURN

    1) Return values indicate a return code from the stored procedure. The return value does not have to be specified as the parameters do. We simply use the RETURN SQL statement to return a value. This value has to be an Integer data type and can return any value you need. For example, this value can be a return code, the number of rows affected by a SQL statement, or the number of rows in a table. Basically, any integer data that you want to return can be specified in return value in your stored procedure.

    2) The RETURN statement exits unconditionally from a stored procedure, so the statements following RETURN are not executed. Though the RETURN statement is generally used for error checking, you can use this statement to return an integer value for any other reason. Using RETURN statement can boost performance because SQL Server will not create a recordset.

Ok, so let us create a stored procedure using above template

Stored procedure with no input parameters


(Using SSMS create new stored procedure and click CTRL + SHIFT + M, this will open a box to comfortable provide parameter values)

StrdPrcSql1.gif
  1. -- ================================================  
  2. SET ANSI_NULLS ON  
  3. GO  
  4. SET QUOTED_IDENTIFIER ON  
  5. GO  
  6. -- =============================================  
  7. -- Author:        Vishal Nayan  
  8. -- Create date: 28-04-11  
  9. -- Description:   Our first Sp  
  10. -- =============================================  
  11. CREATE PROCEDURE Author_Titles  
  12. AS  
  13. BEGIN  
  14.       -- SET NOCOUNT ON added to prevent extra result sets from  
  15.       -- interfering with SELECT statements.  
  16.       SET NOCOUNT ON;  
  17.       BEGIN  
  18.       SELECT a.au_lname, a.au_fname, t.title  
  19.       FROM titles t   
  20.       INNER JOIN titleauthor ta ON t.title_id = ta.title_id   
  21.       RIGHT OUTER JOIN authors a ON ta.au_id = a.au_id  
  22.       RETURN 0            
  23.       END  
  24. END  
  25. GO  

To execute it type below statement on query window

  1. EXEC Author_Titles  
You can also run stored procedures by selecting the sp and clicking Execute Stored procedure. A window will open, since our above stored procedure does not have any input parameter, just click ok. A new query window will open with the below statements;
  1. USE [pubs]  
  2. GO  
  3. DECLARE     @return_value int  
  4. EXEC  @return_value = [dbo].[Author_Titles]  
  5. SELECT      'Return Value' = @return_value  
  6. GO  
See the result below;

StrdPrcSql2.gif

Ok, the query result is fine, but what return value is 0? Well even though we removed the RETURN 0 statement from our stored procedure, the result were same.

Reason
  1. When used with a stored procedure, RETURN cannot return a null value. If a procedure tries to return a null value (for example, using RETURN @status when @status is NULL), a warning message is generated and a value of 0 is returned.

  2. One could say that no RETURN = RETURN NULL = RETURN 0. But no warning is issued because you have not run RETURN NULL. And zero is expected because it's a stored procedure.

Ok, the above stored procedure can also be written in better way. Below stored procedure check whether any previous stored procedure with same name exists or not. If yes, we drop and create new.

  1. USE [pubs]  
  2. GO  
  3. /****** Object:  StoredProcedure [dbo].[Author_Titles]    Script Date: 04/29/2011 00:30:13 ******/  
  4. SET ANSI_NULLS ON  
  5. GO  
  6. SET QUOTED_IDENTIFIER ON  
  7. GO  
  8. -- =============================================  
  9. -- Author:        Vishal Nayan  
  10. -- Create date: 28-04-11  
  11. -- Description:   Our first Sp  
  12. -- =============================================  
  13. IF EXISTS (SELECT * FROM sys.procedures WHERE SCHEMA_ID=SCHEMA_ID('dbo')  
  14. AND name= N'Author_Titles')  
  15. DROP PROCEDURE dbo.Author_Titles  
  16. GO  
  17. CREATE PROCEDURE [dbo].[Author_Titles]  
  18. AS  
  19. BEGIN  
  20.       -- SET NOCOUNT ON added to prevent extra result sets from  
  21.       -- interfering with SELECT statements.  
  22.       SET NOCOUNT ON;  
  23.       BEGIN  
  24.       SELECT a.au_lname, a.au_fname, t.title  
  25.       FROM titles t   
  26.       INNER JOIN titleauthor ta ON t.title_id = ta.title_id   
  27.       RIGHT OUTER JOIN authors a ON ta.au_id = a.au_id  
  28.             RETURN 0    
  29.       END  
  30. END  
  31. GO 

Stored procedure with input parameters

  1. USE [pubs]  
  2. GO  
  3. /****** Object:  StoredProcedure [dbo].[reptq3]    Script Date: 04/29/2011 01:20:52 ******/  
  4. SET ANSI_NULLS ON  
  5. GO  
  6. if exists (select * from sys.procedures where schema_id = schema_id('dbo')  
  7. and name=N'reptq3')  
  8. DROP procedure reptq3  
  9. SET QUOTED_IDENTIFIER ON  
  10. GO  
  11. create PROCEDURE reptq3   
  12. @lolimit money,   
  13. @hilimit money,  
  14. @type char(12)  
  15. AS  
  16. select   
  17.       case when grouping(pub_id) = 1 then 'ALL' else pub_id end as pub_id,   
  18.       case when grouping(type) = 1 then 'ALL' else type end as type,   
  19.       count(title_id) as cnt  
  20. from titles  
  21. where price >@lolimit AND price <@hilimit AND type = @type OR type LIKE '%cook%'  
  22. group by pub_id, type with rollup  
  23. GO  

Execute it.

Result is below

  1. USE [pubs]  
  2. GO  
  3. DECLARE     @return_value int  
  4. EXEC  @return_value = [dbo].[reptq3]  
  5.             @lolimit = 2,  
  6.             @hilimit = 9,  
  7.             @type = N'business'  
  8. SELECT      'Return Value' = @return_value  
  9. GO  
Below is result

Pub_id type cnt
-----------------------------
0736 business 1
0736 ALL 1
0877 mod_cook 2
0877 trad_cook 3
0877 ALL 5
ALL ALL 6

Return Value
------------------------------
0

How to set default values for parameters


We can assign a default value to a parameter by specifying a value in the definition of the parameter. So let us define default values for the above stored procedure "Author_Titles". For that we need to modify it.
  1. USE [pubs]  
  2. GO  
  3. /****** Object:  StoredProcedure [dbo].[Author_Titles]    Script Date: 04/29/2011 22:11:14 ******/  
  4. SET ANSI_NULLS ON  
  5. GO  
  6. SET QUOTED_IDENTIFIER ON  
  7. GO  
  8. ALTER PROCEDURE [dbo].[Author_Titles] @state char(5)='%'      
  9. AS  
  10. BEGIN  
  11.       -- SET NOCOUNT ON added to prevent extra result sets from  
  12.       -- interfering with SELECT statements.  
  13.       SET NOCOUNT ON;  
  14.       BEGIN  
  15.       SELECT a.au_lname, a.au_fname, t.title  
  16.       FROM titles t   
  17.       INNER JOIN titleauthor ta ON t.title_id = ta.title_id   
  18.       RIGHT OUTER JOIN authors a ON ta.au_id = a.au_id  
  19.       WHERE a.state like @state  
  20.       RETURN 0    
  21.       END  
  22. END  
We can also make use of sp_help to know what all parameters are requried for Stored procedure

StrdPrcSql3.gif

How to pass Object names as parameter

 
In SQL Server 2008, if you pass an object name as a parameter to a stored procedure, SQL Server attempts to treat it as a table-valued parameter unless the object name is used either as an argument in a WHERE clause or in a dynamic SQL query

See the below stored procedure, it prompts an error;
  1. CREATE proc find_data @table varchar(128)  
  2. as  
  3. select * from @table  
  4. GO  
  5. Msg 1087, Level 16, State 1, Procedure find_data, Line 3  
  6. Must declare the table variable "@table".  
As you can see, when the parameter is used in the FROM clause, SQL Server expects it to be defined as a table variable.
To use the value in the parameter as a table name, you can build a dynamic SQL query like below;
  1. USE [pubs]  
  2. GO  
  3. /****** Object:  StoredProcedure [dbo].[find_data]    Script Date: 04/30/2011 19:34:50 ******/  
  4. SET ANSI_NULLS ON  
  5. GO  
  6. SET QUOTED_IDENTIFIER ON  
  7. GO  
  8. Create proc [dbo].[find_data] @table varchar(128)  
  9. as  
  10. exec ('select * from ' + @table)  
So when we run this;

exec find_data @table = 'publishers'

StrdPrcSql4.gif

How to use wild card in Stored procedure


Wildcards can be included in varchar-based input parameters and used in a LIKE clause in a query to perform pattern matching. However, you should not use the char data type for parameters that will contain wildcard characters because SQL Server pads spaces onto the value passed in to the parameter to expand it to the specified size of the char data type. For example, if you declared an @lastname parameter as char (40) and passed in 'S%', SQL Server would search not for a string starting with 'S' but for a string starting with 'S', any characters, and ending with up to 38 spaces. This would likely not match any actual
data values.

Let us create a stored procedure which will accept wild card say %
  1. if exists (select * from sys.procedures where schema_id= schema_id('dbo')  
  2. and name =N'sp_wildcard')  
  3. drop procedure sp_wildcard  
  4. go  
  5. create proc sp_widlcard @lastname varchar(40)='%'  
  6. as  
  7. select au_id, au_lname, au_fname  
  8. from authors  
  9. where au_lname like @lastname  
  10. order by au_lname, au_fname  
Points to remember here
  1. If passed no parameter, returns data for all authors in the authors table

  2. If passed a string containing wildcard characters, this procedure returns data for all authors
    Matching the search pattern specified.

  3. If a string containing no wildcards is passed, the query performs a search for exact matches against the string value.

Take a look below

  1. exec sp_widlcard @lastname = 'S%'  
au_id au_lname au_fname
---------------------------------
341-22-1782 Smith Meander
274-80-9391 Straight Dean
724-08-9931 Stringer Dirk

How to use table valued parameters: In previous versions of SQL Server, it was not possible to share the contents of table variables between stored procedures. SQL Server 2008 changes that with the introduction of table-valued parameters, which allow you to pass table variables to stored procedures.

Points to remember
  1. Table-valued parameters are not permitted as the target of a SELECT INTO or INSERT EXEC statement.

  2. Table-valued parameters can be passed only as READONLY input parameters to stored procedures

  3. DML operations, such as UPDATE, INSERT, and DELETE, cannot be performed on table-valued parameters within the body of a stored procedure.

  4. To create and use table-valued parameters, you must first create a user-defined table type and define the table structure. You do so using the CREATE TYPE command

Let us first create a table structure using create type command

  1. if exists (select * from sys.systypes t where t.name='ytdsales_tabletype')  
  2. drop type ytdsales_tabletype  
  3.    
  4. create type ytdsales_tabletype as table  
  5. (  
  6. title_id char(6),  
  7. title varchar(50),  
  8. pubdate date,|  
  9. ytd_sales int)  
  10. go  
After the table data type is created, we can use it for declaring local table variables and for stored procedure parameters. To use the table-valued parameter in a procedure, we create a procedure to receive and access data through a table-valued parameter:
  1. if OBJECT_ID('ab_parm_test'is not null  
  2. drop proc ab_parm_test  
  3. go  
  4. create proc ab_parm_test  
  5. @sales_minimum int =0,  
  6. @pubdate datetime=null,  
  7. @ytd_sales_tab ytdsales_tabletype READONLY  
  8. as  
  9. set nocount on   
  10. if  @pubdate is null  
  11. set @pubdate = dateadd(month,-12,GETDATE())  
  12. select * from @ytd_sales_tab  
  13. where pubdate > @pubdate  
  14. and ytd_sales >=@sales_minimum  
  15. return  
  16. go  

Then, when calling that stored procedure, we declare a local table variable using the table data type defined previously, populate the table variable with data, and then pass the table variable to the stored procedure:

Now we we got some data in our table type , we will execute our stored procedure.

  1. declare @ytd_sales_tab ytdsales_tabletype  
  2. insert  @ytd_sales_tab  
  3. select title_id, convert(varchar(50), title), pubdate, ytd_sales  
  4. from titles  
  5. exec ab_parm_test '6/1/2001', 10000, @ytd_sales_tab  

What are temparory /permannent Stored Procedure

  1. SQL Server enables you to create private and global temporary stored procedures.
    Temporary stored procedures are analogous to temporary tables in that they can be created with the # and ## prefixes added to the procedure name.

  2. The # prefix denotes a local temporary stored procedure; ## denotes a global temporary stored procedure

  3. A local temporary stored procedure can be executed only by the connection that created it, and
    The procedure is automatically deleted when the connection is closed.

  4. A global temporary stored procedure can be accessed by multiple connections and exists until the connection used by the user who created the procedure is closed and any currently executing versions
    Of the procedure by any other connections are completed.

  5. If a stored procedure not prefixed with # or ## is created directly in the temp db database, The stored procedure exists until SQL Server is shut down.

What is deferred name Resolution (applicable to SQL Server 2008)

In SQL Server 2008, the object names that a stored procedure references do not have to exist at the time the procedure is created. SQL Server 2008 checks for the existence of database objects at the time the stored procedure is executed and returns an error message at runtime if the referenced object doesn't exist. The only exception is when a stored procedure references another stored procedure that doesn't exist. In that case, a warning message is issued, but the stored procedure is still created

Points to remember here

  1. When a table or view does exist at procedure creation time, the column names in the referenced Table is validated. If a column name is mistyped or doesn't exist, the procedure is not created

  2. One advantage of delayed (or deferred) name resolution is the increased flexibility when creating stored procedures; the order of creating procedures and the tables they reference does not need to be exact.

  3. Delayed name resolution is an especially useful feature when a stored procedure references a temporary table that isn't created within that stored procedure.

How to view Stored Procedure


we can view the source code for the stored procedure in SQL server 2008 by querying the definition of the object catalog view sys.sql_modules or by using the system procedure sp_helptext.

Example 
  1. exec sp_helptext Author_Titles 
StrdPrcSql5.gif

To view dependencies of the stored procedure, select the stored procedure and click view dependencies.

StrdPrcSql6.gif

How to Modify Stored Procedure


We can use ALTER statement to modify the stored procedure. This has two advantages
  1. Here we don't have to drop the procedure first to make the changes, so it remains available.

  2. Because the stored procedure is not dropped, so we don't have to worry about reassigning permission to it after modifying it.

So all we need is select a stored procedure and we have two ways to change stored procedure.

Pubs->Programmatically->Stored Procedure->Author_Titles (Select this stored procedure) and then

  1. Select Modify option, or

  2. Select "Script Stored Procedure" as "ALTER TO".

Both option open stored procedure in a new window with ALTER keyword.

  1. USE [pubs]  
  2. GO  
  3. /****** Object:  StoredProcedure [dbo].[Author_Titles]    Script Date: 04/29/2011 18:15:00 ******/  
  4. SET ANSI_NULLS ON  
  5. GO  
  6. SET QUOTED_IDENTIFIER ON  
  7. GO  
  8. ALTER PROCEDURE [dbo].[Author_Titles]  
  9. AS  
  10. BEGIN  
  11.       -- SET NOCOUNT ON added to prevent extra result sets from  
  12.       -- interfering with SELECT statements.  
  13.       SET NOCOUNT ON;  
  14.       BEGIN  
  15.       SELECT a.au_lname, a.au_fname, t.title  
  16.       FROM titles t   
  17.       INNER JOIN titleauthor ta ON t.title_id = ta.title_id   
  18.       RIGHT OUTER JOIN authors a ON ta.au_id = a.au_id  
  19.             RETURN 0    
  20.       END  
  21. END  

How to use an Output parameter in stored procedure


If a calling batch passes a variable as a parameter to a stored procedure and that parameter is modified inside the procedure, the modifications are not passed to the calling batch unless you specify the OUTPUT keyword for the parameter when executing the stored procedure.

If we want a procedure to be able to pass a parameter values out from the procedure, we need to use the keyword OUTPUT when creating the procedure.
   
Ok, so let us create a stored procedure which accepts two parameters and one is the output one.
  1. if exists (select * from sys.procedures where schema_id=schema_id('dbo')  
  2. and name =N'ytd_sales')  
  3. drop proc ytd_sales  
  4. go   
  5. create proc ytd_sales  
  6. @title varchar(80) ,   
  7. @ytd_sales int output  
  8. as  
  9. select @ytd_sales =ytd_sales  
  10. from titles  
  11. where title =@title  
  12. return  
The calling batch (or stored procedure) needs to declare a variable to store the returned value. The execute statement must include the OUTPUT keyword as well, or the modifications won't be reflected in the calling batch's variable:
  1. declare @sales_up_to_today int  
  2. exec ytd_sales 'Life Without Fear', @sales_up_to_today output  
  3. PRINT 'Sales this year until today'date: '+  
  4. CONVERT(VARCHAR(10), @sales_up_to_today) + '.'  
Result is below:
------------------------------------
Sales this year until today's date: 111.

Some useful SQL Server system stored procedure

sp_who and sp_who2: Return information about current connections to SQL Server.
sp_help [object_name]: Lists the objects in a database or returns information about a specified object.
sp_helpdb: Returns a list of databases or information about a specified database.
sp_configure: Lists or changes configuration settings.
sp_tables: Lists all tables available.
sp_ helpindex [table] - shows you index info (same info as sp_help)
sp_helpconstraint [table] - shows you primary/foreign key/defaults and other constraints *
sp_depends [obj] - shows dependencies of an object, for example:
sp_depends [table] - shows you what stored procs, views, triggers, UDF affect this table
sp_depends [sproc] - shows what tables etc are affected/used by this stored proc

Conclusion

So in this article we learned how to create procedures and later learned how to manage it with some other concepts.

Hope you enjoyed reading

Cheers


Similar Articles