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.

-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        <Author,,Name>
-- Create date: <Create Date,,>
-- Description:   <Description,,>
-- =============================================
CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName>
      -- Add the parameters for the stored procedure here
      <@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>,
      <@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;

    -- Insert statements for procedure here
      SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
END
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)

-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        Vishal Nayan
-- Create date: 28-04-11
-- Description:   Our first Sp
-- =============================================
CREATE PROCEDURE Author_Titles
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;
      BEGIN
      SELECT a.au_lname, a.au_fname, t.title
      FROM titles t
      INNER JOIN titleauthor ta ON t.title_id = ta.title_id
      RIGHT OUTER JOIN authors a ON ta.au_id = a.au_id
      RETURN 0
      END
END
GO

To execute it type below statement on query window

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;

USE [pubs]
GO
DECLARE     @return_value int
EXEC  @return_value = [dbo].[Author_Titles]
SELECT      'Return Value' = @return_value
GO

See the result below;

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.

USE [pubs]
GO
/****** Object:  StoredProcedure [dbo].[Author_Titles]    Script Date: 04/29/2011 00:30:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        Vishal Nayan
-- Create date: 28-04-11
-- Description:   Our first Sp
-- =============================================
IF EXISTS (SELECT * FROM sys.procedures WHERE SCHEMA_ID=SCHEMA_ID('dbo')
AND name= N'Author_Titles')
DROP PROCEDURE dbo.Author_Titles
GO
CREATE PROCEDURE [dbo].[Author_Titles]
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;
      BEGIN
      SELECT a.au_lname, a.au_fname, t.title
      FROM titles t
      INNER JOIN titleauthor ta ON t.title_id = ta.title_id
      RIGHT OUTER JOIN authors a ON ta.au_id = a.au_id
            RETURN 0
      END
END
GO

Stored procedure with input parameters

USE [pubs]
GO
/****** Object:  StoredProcedure [dbo].[reptq3]    Script Date: 04/29/2011 01:20:52 ******/
SET ANSI_NULLS ON
GO
if exists (select * from sys.procedures where schema_id = schema_id('dbo')
and name=N'reptq3')
DROP procedure reptq3
SET QUOTED_IDENTIFIER ON
GO
create PROCEDURE reptq3
@lolimit money,
@hilimit money,
@type char(12)
AS
select
      case when grouping(pub_id) = 1 then 'ALL' else pub_id end as pub_id,
      case when grouping(type) = 1 then 'ALL' else type end as type,
      count(title_id) as cnt
from titles
where price >@lolimit AND price <@hilimit AND type = @type OR type LIKE '%cook%'
group by pub_id, type with rollup
GO

Execute it.

Result is below

USE [pubs]
GO
DECLARE     @return_value int
EXEC  @return_value = [dbo].[reptq3]
            @lolimit = 2,
            @hilimit = 9,
            @type = N'business'
SELECT      'Return Value' = @return_value
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.

USE [pubs]
GO
/****** Object:  StoredProcedure [dbo].[Author_Titles]    Script Date: 04/29/2011 22:11:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Author_Titles] @state char(5)='%'
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;
      BEGIN
      SELECT a.au_lname, a.au_fname, t.title
      FROM titles t
      INNER JOIN titleauthor ta ON t.title_id = ta.title_id
      RIGHT OUTER JOIN authors a ON ta.au_id = a.au_id
      WHERE a.state like @state
      RETURN 0
      END
END

We can also make use of sp_help to know what all parameters are requried for Stored procedure

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;

CREATE proc find_data @table varchar(128)
as
select * from @table
GO
Msg 1087, Level 16, State 1, Procedure find_data, Line 3
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;

USE [pubs]
GO
/****** Object:  StoredProcedure [dbo].[find_data]    Script Date: 04/30/2011 19:34:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create proc [dbo].[find_data] @table varchar(128)
as
exec ('select * from ' + @table)

So when we run this;

exec find_data @table = 'publishers'

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 %

if exists (select * from sys.procedures where schema_id= schema_id('dbo')
and name =N'sp_wildcard')
drop procedure sp_wildcard
go
create proc sp_widlcard @lastname varchar(40)='%'
as
select au_id, au_lname, au_fname
from authors
where au_lname like @lastname
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

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

if exists (select * from sys.systypes t where t.name='ytdsales_tabletype')
drop type ytdsales_tabletype

create type ytdsales_tabletype as table
(
title_id char(6),
title varchar(50),
pubdate date,|
ytd_sales int)
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:

if OBJECT_ID('ab_parm_test') is not null
drop proc ab_parm_test
go
create proc ab_parm_test
@sales_minimum int =0,
@pubdate datetime=null,
@ytd_sales_tab ytdsales_tabletype READONLY
as
set nocount on
if  @pubdate is null
set @pubdate = dateadd(month,-12,GETDATE())
select * from @ytd_sales_tab
where pubdate > @pubdate
and ytd_sales >=@sales_minimum
return
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.

declare @ytd_sales_tab ytdsales_tabletype
insert  @ytd_sales_tab
select title_id, convert(varchar(50), title), pubdate, ytd_sales
from titles
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 

exec sp_helptext Author_Titles

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

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.

USE [pubs]
GO
/****** Object:  StoredProcedure [dbo].[Author_Titles]    Script Date: 04/29/2011 18:15:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Author_Titles]
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;
      BEGIN
      SELECT a.au_lname, a.au_fname, t.title
      FROM titles t
      INNER JOIN titleauthor ta ON t.title_id = ta.title_id
      RIGHT OUTER JOIN authors a ON ta.au_id = a.au_id
            RETURN 0
      END
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.

if exists (select * from sys.procedures where schema_id=schema_id('dbo')
and name =N'ytd_sales')
drop proc ytd_sales
go
create proc ytd_sales
@title varchar(80) ,
@ytd_sales int output
as
select @ytd_sales =ytd_sales
from titles
where title =@title
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:

declare @sales_up_to_today int
exec ytd_sales 'Life Without Fear', @sales_up_to_today output
PRINT 'Sales this year until today's date: '+
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