Programming Stored Procedures

Creating and Maintaining Databases

previous page next page
Creating and Maintaining Databases

Programming Stored Procedures

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

Rules for Programming Stored Procedures

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:
    CREATE DEFAULT CREATE TRIGGER
    CREATE PROCEDURE CREATE VIEW
    CREATE RULE  

  • 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 local 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 instance of Microsoft® SQL Server™ 2000, 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 2100.

  • 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 megabytes (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 name of the object owner if other users are to 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.

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

SET Statement Options

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 remains in effect only for the execution of the stored procedure; when the stored procedure ends, the original setting is restored.

Examples
A. Create a stored procedure that uses parameters

This example creates a stored procedure that is useful in the pubs database. Given the last and first name of an author, the stored procedure 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

When a message appears stating that the command did not return any data and it did not return any rows, the stored procedure has been created.

Now execute the au_info stored procedure:

EXECUTE au_info Ringer, Anne
GO

Here is the result set:

au_lname au_fname title pub_name
--------- --------- --------------------- ----------------
Ringer Anne The Gourmet Microwave Binnet & Hardley
Ringer Anne Is Anger the Enemy? New Moon Books 

(2 row(s) affected)
B. Create a stored procedure that uses default values for parameters

This example creates 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:

au_lname au_fname pub_name
---------------- ---------------- --------------------
Green Marjorie Algodata Infosystems
Bennet Abraham Algodata Infosystems
O'Leary Michael Algodata Infosystems
MacFeather Stearns Algodata Infosystems
Straight Dean Algodata Infosystems
Carson Cheryl Algodata Infosystems
Dull Ann Algodata Infosystems
Hunter Sheryl Algodata Infosystems
Locksley Charlene Algodata Infosystems 

(9 row(s) affected)
C. Execute a stored procedure that overrides the default value of a parameter with an explicit value

In this example, the stored procedure, showind2, the default value for the @table parameter is titles.

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 is what the stored procedure shows for the authors table:

EXECUTE showind2 authors
GO
TABLE_NAME INDEX_NAME INDEX_ID
---------- ---------- ----------
authors UPKCL_auidind 1
authors aunmind 2 

(2 row(s) affected)

If you do not supply a value, SQL Server uses the default table, titles:

EXECUTE showind2
GO

Here is the result set:

TABLE_NAME INDEX_NAME INDEX_ID
---------- ---------- ----------
titles UPKCL_titleidind 1
titles titleind 2 

(2 row(s) affected)
D. Create a stored procedure using a parameter default of NULL

The parameter default can be the value NULL. In this case, if you do 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 you do 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. Create 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 you do 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:

au_lname au_fname title pub_name
-------- -------- --------------------- -------------------
Dull Ann Secrets of Silicon Val Algodata Infosystems
del Castillo Innes Silicon Val Gastrono Binnet & Hardley
DeFrance Michel The Gourmet Microwave Binnet & Hardley 

(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
au_lname au_fname title pub_name
--------- --------- ---------------------- ----------------
Ringer Anne The Gourmet Microwave Binnet & Hardley
Ringer Anne Is Anger the Enemy? New Moon Books
Ringer Albert Is Anger the Enemy? New Moon Books
Ringer Albert Life Without Fear New Moon Books 

(4 row(s) affected)

See Also

CREATE PROCEDURE

EXECUTE

Effects of SQL-92 Options

Rollbacks in Stored Procedures and Triggers

previous page start next page