A stored procedure is a batch of T-SQL that has been named and stored in the database. In its simplest form, a stored procedure allows you to store a SQL query for later execution. By storing procedures, complex SQL statements can be made available to users with no knowledge of SQL.

Stored procedures are created with the 'CREATE PROCEDURE' statement:

CREATE PROCEDURE usp_fullname 
AS
SELECT FirstName + ' ' + ISNULL(MiddleName, '') + ' ' + LastName 
	AS FullName FROM Person.Contact
WHERE Person.Contact.ContactID = 5

The example above creates a stored procedure to execute the specified SELECT statement. The procedure can be run as follows:

EXEC usp_fullname 
GO

Comments

T-SQL provides syntax for single- and multi-line comments. Single-line comments begin with a double-hyphen, and can be useful for in-line comments. Multi-line comments begin with '/*' and are terminated with '*/', and can be useful to provide documentation of your procedure:

CREATE PROCEDURE usp_fullname
AS
/*
Name: usp_fullname
Description:  displays fullname for a given contact id
Author:  David Ramlakhan

Description                  Date         Changed By
Created procedure            2010-10-18   David Ramlakhan
*/
SELECT FirstName + ' ' + 
ISNULL(MiddleName, '') --use ISNULL to avoid problems with NULL MiddleNames
+ ' ' + LastName 
	AS FullName FROM Person.Contact
WHERE Person.Contact.ContactID = 5
GO

Parameters

T-SQL provides syntax for defining variables by preceding a name with an '@' sign. Stored procedures can be defined to use variables to identify input and output parameters.

CREATE PROCEDURE usp_fullname
@id INT
AS
/*
Name: usp_fullname
Parameters: @id INT
Description:  displays fullname for a given contact id
Author:  David Ramlakhan
Change Log

Description                  Date         Changed By
Created procedure            2010-10-18   David Ramlakhan
Added input parameter	     2010-10-18	  David Ramlakhan
*/
SELECT FirstName + ' ' + ISNULL(MiddleName, '') + ' ' + LastName AS FullName 
FROM Person.Contact
WHERE Person.Contact.ContactID = @id

Output parameters store the results from the procedure and are specified with the OUT or OUTPUT keyword.

CREATE PROCEDURE usp_fullname
@id INT,
@name VARCHAR(50) OUT
AS
/*
Name: usp_fullname
Parameters: 	@id INT
		@name VARCHAR OUT
Description:  displays fullname for a given contact id
Author:  David Ramlakhan
Change Log

Description                  Date         Changed By
Created procedure            2010-10-18   David Ramlakhan
Added input parameter        2010-10-18	  David Ramlakhan
Added output parameter	     2010-10-19   David Ramlakhan
*/
SELECT @name = FirstName + ' ' + ISNULL(MiddleName, '') + ' ' + LastName
FROM Person.Contact
WHERE Person.Contact.ContactID = @id
GO

To execute the stored procedure with an OUTPUT parameter, you need to first declare a variable to store the output from the procedure, and then call the procedure specifying the input and output parameters.

DECLARE @name VARCHAR(50)
EXEC usp_fullname 5,  @name OUTPUT
SELECT @name
GO

You could also declare and initialise your input variables, prior to calling the procedure. In addition to specifying parameters in the correct order, the call to EXEC also accepts named parameters, which allows you to specify the parameters in any order:

DECLARE @name VARCHAR(50),
		@id INT
SET @id = 5
EXEC usp_fullname @name = @name OUTPUT, @id = @id
SELECT @name

An output parameter allows you to return a scalar value to your application. Multiple input and output parameters can be used in the stored procedure definition. To return a resultset to an application, use a SELECT statement at the end of the code block.

Variables are assigned values with either SET or SELECT, and variable values are read with SELECT

Table Variables

Variables can be either any of the types allowed for a table column (except text, ntext or image), or type TABLE. Non-table variables can only hold a single value.

When assigning to a variable via select, a scalar variable will only hold the first field in the last row of the resultset.

CREATE  PROCEDURE usp_populate_varchar @my_varchar VARCHAR(50) OUT
AS 
SELECT @my_varchar = FirstName
FROM AdventureWorks2008R2.Person.Person
GO

DECLARE @people VARCHAR(MAX)
EXEC usp_populate_varchar @my_varchar = @people OUT
SELECT @people

To capture multiple rows and columns from a SELECT statement use a table variable:

CREATE PROCEDURE usp_populate_table
AS
SELECT FirstName, MiddleName, LastName
  FROM AdventureWorks2008R2.Person.Person
GO

DECLARE @people_tbl TABLE (
First VARCHAR(50) NOT NULL,
Middle VARCHAR(50) NULL,
Last VARCHAR(50) NOT NULL)

INSERT @people_tbl(First, Middle, Last)
EXEC usp_populate_table

SELECT * FROM @people_tbl

Table variables can also be used as input parameters to a procedure. To do so, we first define a new type to be the type of our table variable:

CREATE TYPE dbo.People AS TABLE (
  first         VARCHAR(30) NOT NULL,
  middle        VARCHAR(30) NULL,
  last          VARCHAR(30) NOT NULL)
GO

Having defined the type for a table variable, we can use a variable of this type as an input parameter:

CREATE PROC usp_add_rows @table_param People READONLY,
        @userID varchar(5) 
         AS
        INSERT INTO Contacts(firstname, middlename, lastname, modified_by)
        SELECT first, middle, last, @userID
        FROM @table_param
GO

When calling this procedure, we must declare and initialise a variable of the declared type, which can then be used in the procedure call:

-- Instantiate a table variable
DECLARE @data People

-- Initialise the table variable
INSERT INTO @data(first, middle, last)
VALUES ('John', 'Robert', 'Benson'),
        ('Alice', 'Rachel', 'Begum'),
        ('Jane', '', 'Poole'),
        ('Albert', 'Prince', 'Wales')

-- Call the procedure
EXEC usp_add_rows @table_param = @data, @userID = 'fd001'

Global Variables

Global variables are designated by a double-ampersand ('@@') and are read-only.

Flow Control

T-SQL provides a number of flow control structures:

RETURN

Immeadiately terminate execution of procedure returning control back to the calling application. Optional return value can be specified if required

IF...ELSE

Conditional execution. By default only a single statement can be specified as conditional upon to the IF condition. To execute multiple statements conditionally, they should be wrapped in a BEGIN...END block

WHILE

Used to iterate a block while a conditional statement evaluates to true. As with IF, block of statements are delimited by BEGIN...END

WAITFOR

Used to pause execution. WAITFOR should be qualified with one of three keywords plus an appropriate value:

1. WAITFOR DELAY delay_value
2. WAITFOR TIME time_value
3. WAITFOR RECEIVE --Used with Service Broker

WAITFOR RECEIVE is only used in Service Broker setups

GOTO

Used to jump to labelled code

Our current procedure doesn't cope very well when called with an non-existent id. We can use an IF statement to handle this situation:

CREATE PROCEDURE usp_fullname
@id INT,
@name VARCHAR(50) OUT
AS
/*
Name: usp_fullname
Parameters: 	@id INT
		@name VARCHAR OUT
Description:  displays fullname for a given contact id
Author:  David Ramlakhan
Change Log

Description                  Date         Changed By
Created procedure            2010-10-18   David Ramlakhan
Added input parameter	     2010-10-18	  David Ramlakhan
Added output parameter	     2010-10-19   David Ramlakhan
Added conditional execution  2010-10-19	  David Ramlakhan
*/
SELECT @name = FirstName + ' ' + ISNULL(MiddleName, '') + ' ' + LastName
FROM Person.Contact
WHERE Person.Contact.ContactID = @id
IF @name IS NULL
BEGIN
	SELECT @name = 'No such contact: ' + CONVERT(VARCHAR(30), @id)
	RETURN 1
END
ELSE
	RETURN 0

The return value can then be captured in the call to EXEC:

DECLARE @name VARCHAR(50),
		@id INT,
		@result INT
SET @id = 59089787
EXEC @result = usp_fullname @name = @name OUTPUT, @id = @id
SELECT @name AS Fullname, @result AS Status
SET @id = 5
EXEC @result = usp_fullname @name = @name OUTPUT, @id = @id
SELECT @name AS Fullname, @result AS Status

Error Handling

When an error occurs during the execution of a block of T-SQL, processing is terminated immeadiately. The @@ERROR global will hold information on the last error encountered. SQL Server will not automatically rollback a transaction that encounters an error. Set 'XACT_ABORT = ON', to enable transaction rollback on error.

SET XACT_ABORT = ON 
BEGIN TRAN
  ....
END TRAN
SET XACT_ABORT = OFF

To prevent execution of the block terminating prematurely, T-SQL provides a BEGIN TRY... END TRY structure to wrap a block of statements. Any errors encountered in the TRY block can be handled in a BEGIN CATCH...END CATCH block.

Dynamic Execution

T-SQL provides to mechanisms to execute dynamically constructed commands via EXEC or sp_executesql:

DECLARE @backup_date DATE
SET @backup_date = GETDATE()

DECLARE @backup_robots sysname
SET @backup_robots = 'Robots' + CAST(@backup_date AS NVARCHAR)

DECLARE @SQL NVARCHAR(255)
SET @SQL = 'SELECT * INTO [Archive].' + QUOTENAME(@backup_robots) +
		' FROM Robots'

EXEC sp_executesql @SQL

Dynamic constructed commands make excellent targets for injection attacks, and are therefore normally reserved for administrative procedures rather than application procedures

Cursors

Cursors allow you to retrieve a set of rows and then process them one row at a time. However, SQL Server is optimised to work on sets of rows, and cursors should normally be avoided

DECLARE is used to define the SELECT statement to be used as the source for the rows in the cursor. OPEN causes the SELECT statement to be executed and the result rows loaded into the cursor memory structure. FETCH retrieves one row from the cursor at the current pointer position, and moves the pointer on to the next row. CLOSE is used to close processing on the cursor. DEALLOCATE removes the cursor and deallocates the memory stucture containing the cursor resultset

DECLARE cursor_name CURSOR FOR
SELECT (...)

OPEN cursor_name

FETCH cursor_name INTO @var_1, @var_2, ....

WHILE @@FETCH_STATUS = 0
  BEGIN
	FETCH cursor_name INTO @var_1, @var_2, ....
	statements
  END

CLOSE cursor_name

DEALLOCATE cursor_name

We could use a cursor to read from a table a line at a time to remove duplicate lines, or combine fields from one line with the value from the previous line. Consider a table of song lyrics. Each row consists of a song_id, song_title, a line of the lyrics and a sequence number defining the order of the lyric lines. We want to produce a query with one line per song combining the individual lyric lines into one lyric field:

CREATE PROCEDURE usp_display_lyrics
AS
-- Use a temporary table to store a single line for each song
DECLARE @lyric_tbl TABLE(
song_id int,
song_name varchar(50),
lyrics varchar(max),
PRIMARY KEY (song_id))

-- Load the temporary table with song_ids
INSERT INTO @lyric_tbl
SELECT DISTINCT song_id, '', '' FROM lyrics

-- Variables to hold values from the CURSOR
DECLARE @song_id int, @song_name varchar(50), @lyric varchar(max)

-- Variables to hold the previous values from the CURSOR
DECLARE @previous_song_id int, @previous_song_name varchar(50), 
	@previous_lyric varchar(max)

-- Use a newline to join each line of lyrics
-- CHAR(13) = LINEFEED
-- CHAR(10) = CARRIAGE RETURN
DECLARE @spacer varchar(2)
SET @spacer = CHAR(13) + CHAR(10)

-- Set up a cursor on the existing lyrics table
DECLARE lyric_cursor CURSOR FOR
  SELECT song_id, song_name, lyric
  FROM lyrics
  ORDER BY song_id, lyric_seq_no

OPEN lyric_cursor

FETCH lyric_cursor INTO @song_id, @song_name, @lyric

UPDATE @lyric_tbl
SET lyric = @lyric
    song_name = @song_name
WHERE song_id = @song_id

WHILE @@FETCH_STATUS = 0
BEGIN
  -- Store previous values for comparison
  SET @previous_lyric = @lyric
  SET @previous_song_id = @song_id
  SET @previous_song_name = @song_name
  
  -- Fetch the next line of data
  FETCH lyric_cursor INTO @song_id, @song_name, @lyric
  
  IF @song_id = @previous_song_id
  BEGIN
	SET @lyric = @previous_lyric + @spacer + @lyric
	SET @song_name = ISNULL(@previous_song_name, @song_name)
  END

  UPDATE @lyric_tbl
  SET lyrics = @lyric, song_name = @song_name
  WHERE song_id = @song_id
END

CLOSE lyric_cursor

DEALLOCATE lyric_cursor

SELECT * FROM @lyric_tbl

GO
   

CLR Procedures

Stored procedures can be written in any supported CLR language. The routines can be compiled into a DLL and loaded into SQL Server. Then stored procedures can be defined for any public method within the DLL.