Programming Stored Procedures

Almost any Transact-SQL code that can be written as a batch can be used to create a stored procedure.

Stored Procedure Rules

Rules for programming stored procedures include:

• The CREATE PROCEDURE definition itself can include any number and type of SQL statements except for the following CREATE statements, which cannot be used anywhere within a stored procedure:

• Other database objects can be created within a stored procedure. You can reference an object created in the same stored procedure as long as it is created before it is referenced.
• You can reference temporary tables within a stored procedure.
• If you create a private temporary table inside a stored procedure, the temporary table exists only for the purposes of the stored procedure; it disappears when you exit the stored procedure.
• If you execute a stored procedure that calls another stored procedure, the called stored procedure can access all objects created by the first stored procedure, including temporary tables.
• If you execute a remote stored procedure that makes changes on a remote Microsoft® SQL Server™, those changes cannot be rolled back. Remote stored procedures do not take part in transactions.
• The maximum number of parameters in a stored procedure is 1024.
• The maximum number of local variables in a stored procedure is limited only by available memory.
• Depending on available memory, the maximum size of a stored procedure is 128 MB.

For more information about the rules for creating stored procedures, see CREATE PROCEDURE.

Qualifying Names Inside Stored Procedures

Inside a stored procedure, object names used with statements (for example, SELECT or INSERT) that are not user-qualified default to the owner of the stored procedure. If a user who creates a stored procedure does not qualify the name of the tables referenced in SELECT, INSERT, UPDATE, or DELETE statements within the stored procedure, access to those tables through the stored procedure is restricted by default to the creator of the procedure.

Object names used with the statements ALTER TABLE, CREATE TABLE, DROP TABLE, TRUNCATE TABLE, CREATE INDEX, DROP INDEX, UPDATE STATISTICS, and DBCC must be qualified with the object owner’s name if other users are to make use of the stored procedure. For example, Mary, who owns table marytab, must qualify the name of her table when it is used with one of these statements if she wants other users to be able to execute the stored procedure in which the table is used.

This rule is necessary because object names are resolved when the stored procedure is run. If marytab is not qualified and John tries to execute the procedure, SQL Server looks for a table called marytab owned by John.

Encrypting Procedure Definitions

If you are creating a stored procedure and you want to make sure that the procedure definition cannot be viewed by other users, you can use the WITH ENCRYPTION clause. The procedure definition is then stored in an unreadable form.

Once encrypted, the definition of the stored procedure cannot be decrypted and cannot be viewed by anyone, including the owner of the stored procedure or the system administrator.

SET Statement

When an ODBC application connects to SQL Server, the server automatically sets these options for the session:

• SET QUOTED_IDENTIFIER ON
• SET TEXTSIZE 2147483647
• SET ANSI_DEFAULTS ON
• SET CURSOR_CLOSE_ON_COMMIT OFF
• SET IMPLICIT_TRANSACTIONS OFF

These settings increase the portability of ODBC applications. Because DB-Library-based applications generally do not set these options, stored procedures should be tested with the SET options listed above turned both on and off. This ensures that the stored procedures work correctly regardless of the options a particular connection may have set when it invokes the stored procedure. A stored procedure that requires a particular setting for one of these options should issue a SET statement at the start of the stored procedure. This SET statement will remain in effect only for the execution of the stored procedure; when the stored procedure ends, the original setting is restored.

Examples

A. Creating a stored procedure that uses parameters

Here is an example of creating a stored procedure that is useful in the pubs database. Given an author’s last and first name, it displays the title and publisher of each book by that author:

CREATE PROC au_info @lastname varchar(40), @firstname varchar(20)

AS

SELECT au_lname, au_fname, title, pub_name

FROM authors INNER JOIN titleauthor ON authors.au_id = titleauthor.au_id

    JOIN titles ON titleauthor.title_id = titles.title_id

    JOIN publishers ON titles.pub_id = publishers.pub_id

WHERE au_fname = @firstname

AND au_lname = @lastname

GO

You get a message stating that the command did not return any data and it did not return any rows, which means the stored procedure has been created.

Now execute au_info:

EXECUTE au_info Ringer, Anne

GO

Here is the result set:

(2 row(s) affected)

B. Creating a stored procedure that uses default values for parameters

Here is a stored procedure, pub_info2, that displays the names of all authors who have written a book published by the publisher given as a parameter. If no publisher name is supplied, the stored procedure shows the authors published by Algodata Infosystems.

CREATE PROC pub_info2 @pubname varchar(40) = 'Algodata Infosystems'

AS

SELECT au_lname, au_fname, pub_name

FROM authors a INNER JOIN titleauthor ta ON a.au_id = ta.au_id

    JOIN titles t ON ta.title_id = t.title_id

    JOIN publishers p ON t.pub_id = p.pub_id

WHERE @pubname = p.pub_name

Execute pub_info2 with no parameter specified:

EXECUTE pub_info2

GO

Here is the result set:

(9 row(s) affected)

C. Executing a stored procedure that overrides the parameter's default with an explicit value

In the following stored procedure, showind2, titles is assigned as the default value for the @table parameter:

CREATE PROC showind2 @table varchar(30) = 'titles'

AS

SELECT TABLE_NAME = sysobjects.name,

INDEX_NAME = sysindexes.name, INDEX_ID = indid

FROM sysindexes INNER JOIN sysobjects ON sysobjects.id = sysindexes.id

WHERE sysobjects.name = @table

The column headings (for example, TABLE_NAME) make the results more readable. Here’s what the stored procedure shows for the authors table:

EXECUTE showind2 authors

GO

If the user does not supply a value, SQL Server uses the default table, titles:

EXECUTE showind2

GO

Here is the result set:

D. Creating a stored procedure using a parameter default of NULL

The parameter default can be the value NULL. In this case, if the user does not supply a parameter, SQL Server executes the stored procedure according to its other statements. No error message is displayed.

The procedure definition can also specify that some other action be taken if the user does not give a parameter. For example:

CREATE PROC showind3 @table varchar(30) = NULL

AS IF @table IS NULL

    PRINT 'Give a table name'

ELSE

    SELECT TABLE_NAME = sysobjects.name,

    INDEX_NAME = sysindexes.name, INDEX_ID = indid

    FROM sysindexes INNER JOIN sysobjects

    ON sysobjects.id = sysindexes.id

    WHERE sysobjects.name = @table

E. Creating a stored procedure using a parameter default including wildcard characters

The default can include wildcard characters (%, _, [] and [^]) if the stored procedure uses the parameter with the LIKE keyword. For example, showind can be modified to display information about the system tables if the user does not supply a parameter:

CREATE PROC showind4 @table varchar(30) = 'sys%'

AS SELECT TABLE_NAME = sysobjects.name,

    INDEX_NAME = sysindexes.name, INDEX_ID = indid

FROM sysindexes INNER JOIN sysobjects

ON sysobjects.id = sysindexes.id

WHERE sysobjects.name LIKE @table

The following variation of the stored procedure au_info has defaults with wildcard characters for both parameters:

CREATE PROC au_info2 @lastname varchar(30) = 'D%',

    @firstname varchar(18) = '%'

AS

SELECT au_lname, au_fname, title, pub_name

FROM authors INNER JOIN titleauthor ON authors.au_id = titleauthor.au_id

    JOIN titles ON titleauthor.title_id = titles.title_id

    JOIN publishers ON titles.pub_id = publishers.pub_id

WHERE au_fname LIKE @firstname

    AND au_lname LIKE @lastname

If au_info2 is executed with no parameters, all the authors with last names beginning with the letter D are displayed:

EXECUTE au_info2

GO

Here is the result set:

(3 row(s) affected)

This example omits the second parameter when defaults for two parameters have been defined, so you can find the books and publishers for all authors with the last name Ringer:

EXECUTE au_info2 Ringer

GO

(4 row(s) affected)

See Also