by Mark Spenik
Does the topic of stored procedures fall into the realm of developers or DBAs? In the first edition of the SQL Server DBA Survival Guide, after much thought and consideration, we decided that the topic belonged to developers. After a few appreciated e-mail messages from readers of the first edition, we realized that we had to include stored procedures in the second edition. Knowledge of stored procedures is required for DBAs and developers alike. As a DBA, you use stored procedures quite frequently. Microsoft supplies many stored procedures you will use to perform database and system maintenance. (Note: A list of the system stored procedures is in Appendix C.) You will find that you are frequently required to write your own stored procedures to perform specific DBA tasks for your organization or to help a group of developers solve a complex business problem.
A stored procedure is a series of precompiled SQL statements and control-of-flow language statements. Stored procedures can enhance standard SQL by allowing you to use parameters, make decisions, declare variables, and return information. You can think of a stored procedure as a program or function that is stored as a database object on SQL Server. When a stored procedure is executed, the stored procedure runs on SQL Server--not on the client issuing the request. A stored procedure can be a simple SQL statement such as this one:
Select * from authors
A stored procedure can also be a series of complex SQL statements and control-of-flow
language statements that apply a complex business rule or task to a series of tables
in the database.
TIP: A trigger is a special type of stored procedure that automatically executes when certain table data modifications are made (for example, inserts, updates, or deletes). Triggers are used to cascade changes to related tables or to perform complex restrictions that cannot be done with constraints.
Before further defining a stored procedure, here are some of the pros and cons of using stored procedures:
Stored Procedure Pros:
TIP: Using stored procedures as a security mechanism is one way you can prevent users from accidentally modifying a table with an ad-hoc query tool such as MS-Query or Microsoft Access. Grant the users read-only access to the table and then create stored procedures to perform data modifications like UPDATE or DELETE.
Stored Procedure Cons:
TIP: One successful method I have used to keep track of stored procedures is to produce a catalog of stored procedures using a Windows help file. Find a utility that allows you to easily create Windows help files. Create a help file to catalog all your stored procedures. Include the name of the procedure, a description of the procedure, a list and description of procedures, tables, or views that procedure accesses, and other procedures called. As you add, modify, and delete stored procedures, keep the help file up to date. The help files are easy to distribute and have very good search utilities. However, this approach requires the cooperation of anyone who creates or modifies stored procedures on your system to keep the help file up to date.
In the preceding definition of a stored procedure, we stated that a stored procedure consists of precompiled SQL statements--but what exactly does it mean when we say precompiled or compiled? Use Figure 24.1 and walk through the steps SQL Server performs when a stored procedure is created and compiled.
The first step in creating a stored procedure is to add the valid Transact SQL statements and valid control-of-flow language statements that make up the stored procedure.
Once a stored procedure is written and sent to SQL Server to be saved, SQL Server parses the text, looking for invalid Transact SQL statements or invalid objects. If a problem is found, the client connection attempting to save the stored procedure is alerted of the problem and the process ends. You must correct all syntax and invalid object errors before SQL Server can save the stored procedure.
NOTE: The exception to the invalid object rule is when you call other stored procedures. If a stored procedure called by your new stored procedure does not exist at the time you create the new stored procedure, you are given a warning message and the parsing process continues.
Once SQL Server has validated the Transact SQL statements and the objects used, the ASCII text used to create the stored procedure is saved in the database system table syscomments.
When the text of the stored procedure is saved in syscomments, resolution occurs. Resolution is the creation of the normalized form of the procedure (called a query tree) that is stored in the database system table sysprocedures. During resolution, external objects such as table names are translated to their internal representation (table names are translated to object IDs from sysobjects) and the SQL statements are converted to a more efficient form. Figure 24.2 shows an example of a query tree used by a stored procedure that performs the following SQL statement:
Select * from pubs..authors
Compilation is the process of creating the query (access) plan and loading the query plan used by the stored procedure in the procedure cache. Compilation does not occur until the stored procedure is executed for the first time. When the procedure is first executed, the query tree of the procedure is read from the sysprocedures table into the procedure cache. The SQL Server query optimizer then creates the query plan for the stored procedure. The following factors determine the query plan created for a stored procedure:
TIP: Because the query plans are held in memory (procedure cache), the access plans for stored procedures are lost when SQL Server shuts down. The query plan is re-created when SQL Server is restarted and the procedure is executed for the first time.
Once a stored procedure query plan is placed into the procedure cache, the plan stays in the procedure cache as long as there is space in the cache. Subsequent calls to the stored procedure are much faster because the query plan already exists and does not have to be re-created. So what does it mean to be compiled? It means that an access plan (query plan) for the stored procedure exists in the procedure cache.
TIP: Triggers are compiled only once, when the trigger is first created or modified.
An important fact to remember about stored procedures is that they are reusable: once a user executes a stored procedure and the procedure completes, another user can execute the same copy of the stored procedure in the procedure cache. However, stored procedures are not reentrant. To understand what it means when we say a stored procedure is not reentrant, walk through the example shown in Figure 24.3.
User1 begins to execute a stored procedure, usp_test1, that is already in the procedure cache. Before usp_test1 can complete execution, User2 begins to execute the same procedure. Stored procedures are not reentrant, meaning that User2 can't execute the same copy of usp_test1 while User1 is executing it. Instead, SQL Server creates another query plan for usp_test1 and loads it in the procedure cache for User2 to execute. Now two copies of usp_test1 exist in the procedure cache. An interesting point to make (and one that is discussed in detail later) is that not only do two copies of usp_test1 exist in the procedure cache, but in this example each copy of the stored procedure has a different query plan. Remember that several factors determine the type of access plan created by the query optimizer. In this example, User1 passed in a different parameter than User2. The access plan of User1's copy of usp_test1 performs a table scan but the access plan of User2's copy of usp_test1 uses a clustered index. After both users' stored procedures complete, two very different query plans for usp_test1 exist in the procedure cache. If User1 executes the procedure again, there is no way to determine which copy of the stored procedure he or she will get. This uncertainty can cause a performance problem if User1 passes a parameter that would execute faster using a clustered index but uses the stored procedure that performs a table scan instead. This problem is addressed in the following section when discussing the RECOMPILE option.
To create a stored procedure using SQL Server 6.5, use the Create Procedure statement, which has the following syntax:
Create PROCEDURE
[owner.]procedure_name[;number][(parameter1[,parameter2]...[parameter255])]
[{FOR REPLICATION} | {WITH RECOMPILE} [{[WITH]|[,]}ENCRYPTION]] As SQL Statements
NOTE: When you use the Create Procedure statement, you can type the statement Create Procedure as in the following example:Create Procedure usp_test
Alternatively, you can use the shortcut statement Create Proc as in the following example:
Create Proc usp_test
In this syntax, procedure_name is the name of the stored procedure. Stored procedure
names conform to the standard SQL Server naming conventions. The maximum size of
a procedure name is 30 characters. With SQL Server 6.5, you can create a local temporary
stored procedure by adding # to the beginning of the procedure name. A local
temporary stored procedure can be used only by the connection that created the stored
procedure; it is dropped when the connection ends. You can create a global temporary
stored procedure by adding ## to the beginning of the stored procedure name.
A global temporary stored procedure can be used by all user connections and is dropped
at the end of the last session using the procedure. Suggested stored procedure naming
conventions are found in Appendix A.
Stranger than Fiction!
The Microsoft Transact SQL Reference, with Books Online, incorrectly states the maximum size of a stored procedure name to be 20 characters.
Also in this syntax, number is an optional integer that can be used to group stored procedures with the same name so that they can be dropped with a single drop statement. parameter allows parameters to be passed into the stored procedure. Parameters are optional; a stored procedure can have up to 255 parameters. The text For Replication marks a stored procedure for use only by the replication process.
When the WITH RECOMPILE option is used, the query plan of the stored
procedure is not kept in the procedure cache. A new query plan is generated every
time the procedure is executed. You cannot use the FOR REPLICATION and WITH
RECOMPILE options together.
Solving the WITH RECOMPILE Myth
The WITH RECOMPILE option is a highly misunderstood option. I have heard things like, "Why would you ever use that? When you use the WITH RECOMPILE option with a stored procedure, you lose the advantage of a compiled query plan. You might as well use straight SQL!" Did you ever think that ignorance is bliss and no wonder your system is slow? When using the WITH RECOMPILE statement, you do lose the benefit of a compiled query plan, but the stored procedure is still faster than straight SQL because the SQL text has already been parsed and a query tree has been constructed. Even more important, in cases when you use the WITH RECOMPILE option, you lose the small amount of time it takes to create the query plan as opposed to using an incorrect query plan that may cost minutes or hours. When do you use the WITH RECOMPILE option? When you are passing into a stored procedure parameters that differ greatly in data distribution; these different parameters would cause the query optimizer to create different query plans (refer back to the example in the section, "Reusable but Not Reentrant," and refer to Figure 24.3). Also consider using the WITH RECOMPILE option if you pass parameters into a stored procedure and the time to execute the stored procedure is inconsistent. You can force a stored procedure to recompile the next time it is executed by executing the system stored procedure sp_recompile on a table or view referenced in the stored procedure.
The WITH ENCRYPTION option encrypts the text of the stored procedure in the syscomments table so that users cannot query the text of the procedure. Use this option only when necessary.
SQL Statements are the SQL statements or control-of-flow language statements that make up the stored procedure.
Now look at some simple examples of creating stored procedures in the pubs database. The following example creates a stored procedure called usp_show_authors that selects all the columns and rows from the authors table:
Create Procedure usp_show_authors
as
Select * from authors
The next example creates a stored procedure called usp_Texas_Publisher that selects all the publisher's names from the publishers table using the WITH ENCRYPTION option:
Create Proc usp_Texas_Publisher WITH ENCYPTION
as
Select pub_name
From publishers
Where state = `TX'
TIP: Stored procedures are created in the database from which the Create Procedure command is executed; the exception is temporary stored procedures, which are created in the tempdb database.
Use the SQL Query tool (refer back to Figure 24.2) to enter the procedure name and click the green arrow to execute the stored procedure. If the stored procedure is not the first statement, use the EXECUTE statement as follows:
execute procedure_name
Here is a shortcut and commonly used format:
exec procedure_name
Before getting into some of the finer points of stored procedures (like using
parameters or returning values), it is important to cover a very important topic:
control-of-flow language. Control-of-flow language gives your stored procedures added
capabilities not found in standard Transact SQL. Using control-of-flow language,
you can make use of standard programming techniques such as performing loops or conditional
processing on variables.
TIP: Want to learn how to write good stored procedures? The first requirement is to learn SQL and the many additional Transact SQL features. The next step is to make yourself familiar with the different control-of-flow statements and their uses. Learn these fundamentals, and you are on your way to writing good stored procedures.
The following control-of-flow language statements are quickly reviewed in the following sections:
Additionally, the following Transact SQL extensions are reviewed in the following sections:
Using the Declare statement, you can create variables using standard SQL Server datatypes. Variables defined with the Declare statement must begin with the @ symbol. You can declare more than one variable in a Declare statement by using commas to separate the variables. The syntax for Declare is as follows:
Declare @parm1 datatype [,@parm2 datatype ...]
The following example creates three parameters of different datatypes:
Declare @count int, @current_date datetime
Declare @My_Message varchar(255)
NOTE: To initialize or set the value of a variable you create with the Declare statement, use the keyword SELECT. For example, the following statement sets the value of the variable @count to 100:Select @count = 100
The following example sets the variable @count to the total number of rows in the authors table located in the pubs database:
Select @count = count(*)
from pubs..authors
The GOTO statement performs the same function it performs in many different programming languages like C and Visual Basic. GOTO jumps the execution of the stored procedure to the associated label. The syntax for GOTO is as follows:
GOTO label
In the following example, the GOTO statement jumps over (skips) the SELECT statement and executes the UPDATE statement:
GOTO do_update
SELECT * from authors
do_update:
UPDATE authors
set au_lname = "Spenik"
Where state = 'VA'
NOTE: When defining a label, the label name must end in a colon. When using the GOTO statement, refer to the label name but do not include the colon.
BEGIN and END statements are considered block statements because they group a series of SQL statements and control-of-flow language into a single entity. BEGIN and END are often used with IF...ELSE blocks. The syntax for BEGIN and END is as follows:
BEGIN
{SQL Statements | statement block}
END
The IF and ELSE statements allow you to check for conditions and execute SQL statements based on the condition. The IF statement checks expressions that return a TRUE or FALSE value (that is, it checks Boolean expressions). If the value returned is TRUE, the block of statements or the single statement that follows the IF statement is executed. If the value returned is FALSE, the optional ELSE statement is executed. The syntax for the IF...ELSE statement is as follows:
IF Boolean Expression
{SQL statement | Statement Block}
[ELSE [Boolean Expression]
{SQL statement | Statement Block}]
Following is an example of IF and IF...ELSE using a single SQL statement and a statement block:
If @count = 0
Select * from authors
else
Select * from titles
if @Total != 0
begin
Select count(*) from authors
Select count(*) from titles
end
else
begin
Select * from authors
Select * from titles
end
The WAITFOR statement is a delay that allows your stored procedure to wait for a specified time period or until a specific time before execution continues. The syntax for the WAITFOR statement is as follows:
WAITFOR {DELAY `time' | TIME `time'}
A WAITFOR statement can delay for up to 24 hours. The TIME and DELAY options use the format `hh:mm:ss'. The following example causes a delay of 10 seconds:
WAITFOR DELAY '00:00:10'
The following example waits until 11 a.m.:
WAITFOR TIME '11:00:00'
The RETURN statement causes a stored procedure to exit and return to the calling procedure or application. The syntax for RETURN is as follows:
RETURN [Integer Value]
You can return an integer value to the calling routine or application using the following syntax:
exec @status = procedure_name
The following example consists of two parts. The first part is a stored procedure, called usp_return, that returns an integer to the calling procedure or application. The second part is a stored procedure, called usp_call, that calls the stored procedure usp_return and checks the returned value:
Create Procedure usp_return
as
Declare @ret_val int
Select @ret_val = 0
Return @ret_val
Create Procedure usp_call
as
Declare @status int
exec @status = usp_return
if(@status = 0)
Print "Value returned is zero"
When executing stored procedures, SQL Server uses the value 0 to indicate success. Negative values indicate that an error has occurred. The return values -1 to -99 are reserved for SQL Server. When you use the RETURN statement, you can return only integer values; if you want to return other datatypes, you must use an output parameter, as described later in this chapter.
The WHILE statement allows you to perform repeating conditional loops for the execution of a SQL statement or a statement block. The BREAK statement causes an exit from the WHILE loop; CONTINUE causes the WHILE loop to restart, skipping any statements that follow the CONTINUE statement. These statements have the following format:
WHILE Boolean Expressions
{SQL statement | statement block}
[BREAK | CONTINUE]
Examples of these statements can be found in the sample stored procedures shown at the end of this chapter.
The Print statement allows you to return a message to the client's message handler. The message can have up to 255 characters (the maximum size of a varchar datatype). The Print statement has the following syntax:
Print {` Any text message' | @local variable | @@global variable}
If you print a local or global variable, the variable must be a char or varchar datatype; otherwise, you have to convert the datatype to a char or varchar before printing. Following are some examples using the Print statement:
Declare @msg varchar(255), @count int
Select @count = 0
Print "Starting the procedure"
While @count < 5
Begin
Select @count = @count + 1
Select @msg = "The Value of @count is " + str(@count)
Print @msg
end
Select @msg = "This about wraps this procedure up."
Select @msg = @msg + " The value of @count is " + str(@count)
Print @msg
Listing 24.1 shows the output generated from the preceding Print statements, executed from the SQL Query tool.
(1 row(s) affected)
Starting the procedure
(1 row(s) affected)
(1 row(s) affected)
The Value of @count is 1
(1 row(s) affected)
(1 row(s) affected)
The Value of @count is 2
(1 row(s) affected)
(1 row(s) affected)
The Value of @count is 3
(1 row(s) affected)
(1 row(s) affected)
The Value of @count is 4
(1 row(s) affected)
(1 row(s) affected)
The Value of @count is 5
(1 row(s) affected)
(1 row(s) affected)
This about wraps this procedure up. The value of @count is 5
TIP: Use Print statements in your stored procedures during testing to help debug the stored procedure. Use the Print statement like a trace statement to print out particular locations or values in the procedure.
The RAISERROR statement sets a SQL Server system flag to signify that an error has occurred and sends back an error message to the client connection. The syntax for RAISERROR is as follows:
RAISERROR ({message_id | message_str}, severity, state [,arg1[,arg2]]) [WITH LOG]
Use the WITH LOG option to write the error message to the SQL Server error log and the Windows NT event log.
Comments in a stored procedure follow the C programming language standard. Comments begin with a /* and end with a */. Everything between the /* and the */ is considered part of the comment. Use a lot of comments to make your stored procedures easy to read and maintain. Following are some examples of comments:
/* This is a one line comment */
/* This
Comment
Spans
Several lines */
/*
** This is a comment - I think the added ** makes it easier to read.
*/
Parameters allow you to write flexible stored procedures by executing the SQL statements with values that are determined at run time (not at compile time) and that can be changed at every execution. Parameters follow the same naming conventions as standard stored procedure variables (they must begin with @). Parameters can be input parameters or output parameters.
Input parameters are used to pass values into a stored procedure. Input parameters have the following syntax:
Create proc Procedure_name @parm1 datatype, @param2 datatype
The following example defines three input parameters of different datatypes:
Create procedure usp_input @temp_name varchar(30), @total int, @current_date datetime
You can pass the values into a stored procedure in several ways. The standard way to pass the values is as follows:
exec usp_input `Spenik & Sledge', 1000, '03/25/96'
When you use the preceding calling form, you must pass the values in the same order as they are declared in the stored procedure. Instead of passing in values, you can also pass in other variables (of the same datatype) as follows:
exec usp_input @authors_name, @new_total, @my_date
Another way to pass parameters is by using the parameter name. When using the parameter name, you can pass the parameters in any order--but if you use the name for one parameter, you must use the parameter name for all parameters that follow. The following is a valid example of using parameter names:
exec usp_input @total = 1000, @temp_name = `Spenik & Sledge', @current_date = '03/25/96'
The following example is not valid because, once you start using a parameter name, all the parameters that follow must also include the name:
exec usp_input `Spenik & Sledge', @total = 1000, '03/25/96'
Input parameters can also have default values. To assign a default value to an input parameter, use the following syntax:
@parameter_name datatype = default_value
For example:
create proc usp_param @p1 int, @p2 int = 3, @p3 int
If the procedure is called without a parameter (as in the following example), the default value is used:
exec usp_param @p1=5, @p3=100
To use an input parameter in a stored procedure, reference the variable name (just like any other variable). The following example checks the value of the parameter; if the parameter is less than 0, a message is printed:
create procedure usp_test @p1 int
as
if @p1 < 0
print "The value is less than zero"
Output parameters are used to return values in a variable to a calling stored procedure or application. The syntax to declare an output parameter is as follows:
Create procedure proecdure_name @parameter_name datatype OUTput
The following example declares an output parameter called @p1:
create procedure usp_test @p1 int OUT
To call a procedure with an output parameter, you must declare a variable to hold the returned value (the variable does not have to have the same name as the output parameter used in the create procedure statement). Then use the keyword out (output) as follows:
exec usp_test @v1 out
After the procedure executes, you can then use the value in the output parameter (in this example, @v1) for further processing. You can also use the parameter name with output parameters, as follows:
exec usp_test @p1 = @v1 out
The following example multiplies an input parameter by 2 and returns the string version of the result in an output parameter:
Create Procedure usp_Double_Value @old_value int, @new_value
varchar(20) OUT
as
Declare @temp_value int
Select @temp_value = @old_value * 2
Select @new_value = convert(varchar(20), @temp_value)
The following example uses the procedure usp_Double_Value and prints out the results:
Declare @start_value int, @computed_value varchar(20)
Declare @msg varchar(50)
Select @start_value = 20
exec usp_Double_Value @start_value, @computed_value OUT
Select @msg = "The computed value is " + @computed_value
Print @msg
Listing 24.2 shows the output generated from the preceding routine when executed from the SQL Query tool.
(1 row(s) affected)
(1 row(s) affected)
The computed value is 40
SQL Server provides several global variables you can use when writing stored procedures. A global variable is distinguished from a standard variable by the @@ that precedes the name. Following is a list of some of the commonly used global variables:
Following is a list of some quick facts, tips, helpful hints, and restrictions that apply to stored procedures:
Let's See You Do this with Sybase
SQL Server 6.x has greatly enhanced the EXECUTE command that also exists in Microsoft SQL Server 4.2x and current Sybase systems. For starters, you can use the EXECUTE statement to generate dynamic SQL statements and execute them all at run time. Several of the examples at the end of this chapter use dynamic SQL to perform tasks that are not possible in older versions of Transact SQL. When creating dynamic SQL, remember that you must have the proper access to all the objects you plan to use in the dynamic SQL statement; you are limited to generating dynamic SQL statements that do not exceed the maximum size of a char or varchar datatype (255 characters). On the performance side, keep in mind that dynamic SQL is parsed and compiled at the time the EXECUTE statement is issued. Here's a hot new feature added to SQL Server 6.5: you can now use the INSERT INTO command and the EXECUTE statement to populate a local database table with information from a remote server using a remote stored procedure. This is a really hot feature, asked for by many of my clients.
Following are some useful tips and tricks we have learned over the years to help keep track of stored procedures. There are also some tips for testing the stored pro-cedures.
To make your stored procedures easier to read and maintain, add a header to the start of your stored procedure. Following are two examples of headers you can use in your stored procedures.
Example header style 1:
Create Procedure usp_proc_name as
/****************************************************************************
** Name:
**
** Description:
**
** Parameters:
**
** Returns: 0 - Success
** -1 - Error
**
** Other Outputs: Populates the table xxxx for Access reports.
**
** History:
** Mark A. Spenik KSCS, 10/17/95 Initial Release.
**
******************************************************************************/
Example header style 2:
Create Procedure usp_proc_name as
/*--------------------------------------------------------------
Procedure Name
----------------------------------------------------------------
Description:
Called By:
Parameters:
Status Returns:
Other Outputs:
Example: <show an example of calling the stored procedure>
------------------------------------------------------------------
History:
----------------------------------------------------------------*/
When testing stored procedures from the SQL Query tool, you may want to know exactly how long a stored procedure takes to execute. To time the stored procedure and display the start and end times in hh:mm:ss:ms format, use the functions getdate() and convert as follows:
select convert(varchar(20), getdate(), 14) `Start Time'
exec Procedure_name
select convert(varchar(20), getdate(), 14) `End Time'
For a stored procedure that returns several rows of data, you can use the following timing routine, which displays both the start and end times at the end of the stored procedure:
declare @startmsg varchar(40)
declare @endmsg varchar(40)
select @startmsg = `Start Time: ` + convert(varchar(20), getdate(), 14)
/* Execute stored procedure */
exec Procedure_name
select @endmsg = `End Time: ` + convert(varchar(20), getdate(), 14)
print @startmsg
print @endmsg
How do you test a large, complex stored procedure in SQL Server when Microsoft does not provide a debugger? One way to test a large, complex stored procedure is to start a session using the SQL Query tool or ISQL/w. Break the stored procedure into parts, testing each part of the stored procedure and validating the results of each part.
I meet many individuals who have trouble returning the correct number of rows when performing complex joins or using subqueries. One way to determine whether your WHERE clause is correct is to change the SQL statement into SELECT count(*). You can quickly run the complex SQL statement and determine by the row count whether the WHERE clause is correct. The following example shows how to replace an UPDATE statement with SELECT count(*) to validate the WHERE clause. Here's the original code fragment:
UPDATE table_a
Set my_name = table_b.old_name
FROM table_a, table_b
WHERE table_a.id = table_b.id
AND table_a.birth_date IN (Select *
From table_c)
To test the WHERE clause, change the UPDATE statement to SELECT count(*) as follows:
Select count(*)
FROM table_a, table_b
WHERE table_a.id = table_b.id
AND table_a.birth_date IN (Select *
From table_c)
You would be surprised how many times someone asks me, "How do you subtract a date with Microsoft SQL Server? I saw the dateadd() function, but I did not see a datesubtract() function, so what's the deal?" The deal is simple: to subtract a date, use the dateadd() function with a negative number. The following example subtracts 10 days from the current day's date:
select dateadd(day,-10,getdate())
Stored procedures are limited to 65,025 characters of text. The limitation is a result of storing the text of a stored procedure in the syscomments table, where each procedure can have up to 255 rows of 255 bytes each (that is, 255 rows x 255 bytes = 65,025 bytes). To determine the size of a stored procedure, you can use DBCC MEMUSAGE, which displays the size of the 12 largest objects in the procedure cache. You can determine how many rows the procedure is currently using in syscomments and how many rows are left by issuing the following statement:
Select count(*) "Number of rows used", 255
- count(*) "Number of rows remaining"
From syscomments where id = object_id("your procedure name")
Have you ever received a call from a developer who was running an application that just received an error number (for this example, assume the error number 7935), and the developer wanted to know what the error number meant? The following stored procedure takes an error number as an input parameter and returns the severity and the description of the error number from the sysmessages table (blanks are returned for error numbers that do not exist in sysmessages).
create procedure usp_Show_Error_Message @error_number
int
as
Select severity "Error Severity", description "Error Message"
from master..sysmessages
where error = @error_number
You can reduce the amount of extraneous information sent back by a stored procedure (for example, the message N rows affected) by placing the following statement at the top of your stored procedure:
SET NO COUNT ON
When you use the Set NOCOUNT ON statement, you limit the amount of extraneous information sent back to the calling process, thus reducing network traffic and increasing performance. The following example, created in the pubs database, shows how to use the statement in a stored procedure:
Create procedure usp_nocount
as
SET NOCOUNT ON
Select * from authors
A cursor is a SQL result set that allows you to perform row-oriented operations on the result set (this differs from standard SQL result sets, which return all the rows in the result set). Cursors make it possible to process data row by row. With SQL Server's cursors, you can navigate forward and backward through the result set. You can really exploit the power of cursors when you combine them with the EXEC command and a string variable substitution.
NOTE: ANSI standard cursors are new with SQL Server 6.0. Earlier versions of SQL Server required cursors to be processed at the client through DB-Library function calls or through ODBC.
Cursors can be used in the following locations:
Every cursor must have at least four components. The four key components must follow this order:
The DECLARE statement contains the user-defined name used to reference
the result set, as well as the SQL SELECT statement that generates the result
set. Think of the DECLARE statement as a temporary table that contains a
pointer to your actual data source.
Syntax
DECLARE cursor_name [INSENSITIVE] [SCROLL] CURSOR
FOR select_statement
[FOR {READ ONLY | UPDATE [OF column_list]}]
cursor_name | The cursor name. |
INSENSITIVE | Specifies that changes in your data source will not be reflected in the cursor. Updates are not allowed to a cursor when this option is specified. |
SCROLL | Allows the following FETCH commands to be used: PRIOR, FIRST, LAST, ABSOLUTE n, and RELATIVE n. |
select_statement | A SQL SELECT statement. The following SQL commands force a cursor to be declared as INSENSITIVE: DISTINCT, UNION, GROUP BY, and/or HAVING. |
READ ONLY | Prohibits updates from occurring against the cursor. |
UPDATE [OF column_list] | Allows updates to be performed against the cursor. The optional clause [OF column_list] specifies which columns in the cursor can be updated. |
declare pub_crsr cursor
for
select pub_id,pub_name
from publishers
Example 2: Read-Only Cursor
declare pub_crsr cursor
for
select pub_id,pub_name
from publishers
FOR READ ONLY
Example 3: Cursor that Allows Updates
declare pub_crsr cursor
for
select pub_id,pub_name
from publishers
FOR UPDATE
Once you declare a cursor, you must open it. The OPEN statement should
immediately follow the DECLARE statement.
Syntax
OPEN cursor_name
cursor_name The name of the cursor to open.
Example
OPEN pub_crsr
After the cursor has been opened, you can retrieve information from the result
set on a row-by-row basis. SQL Server 6.x is one of the few RDBMS products that provides
forward-scrolling cursors and backward-scrolling cursors.
Syntax
FETCH [[NEXT | PRIOR | FIRST | LAST | ABSOLUTE n | RELATIVE
n] FROM] cursor_name
[INTO @variable_name1, @variable_name2, ...]
NEXT Retrieves the next row.
PRIOR Retrieves the preceding row.
FIRST Retrieves the first row.
LAST Retrieves the last row.
ABSOLUTE Retrieves a row based on the absolute position within the result set.
RELATIVE Retrieves a row based on the relative position within the result set.
TIP: Use negative numbers to move backward within a result set when using the ABSOLUTE and RELATIVE arguments. When you use ABSOLUTE, the rows are counted backward from the last row in the recordset. When you use RELATIVE, the rows are counted backward from the current position in the recordset.
cursor_name The name of the cursor. INTO @variable_name1, @variable_name2, and so on Copies the contents of a column into
a variable.
Example 1: Return the Next Row in the Result Set
fetch next from pub_crsr
Example 2: Return the 5th Row in the Result Set
fetch absolute 5 from pub_crsr
Example 3: Copy the Contents of the Next Row into Host Variables
fetch next from pub_crsr into @pub_id,@pub_name
After you finish processing the cursor, you must CLOSE or DEALLOCATE the cursor. The CLOSE statement closes the cursor but does not release the data structures used by the cursor. Use this statement if you plan to reopen the cursor for subsequent use. The DEALLOCATE statement closes the cursor and releases the data structures used by the cursor.
TIP: Always CLOSE or DEALLOCATE a cursor as soon as processing is complete. Cursors consume resources, such as locks, memory, and so on. If these resources are not released, performance and multi-user problems may arise.
Syntax
CLOSE cursor_name
DEALLOCATE cursor_name
cursor_name The cursor name.
Example 1: Close a Cursor
CLOSE pub_crsr
Example 2: Deallocate a Cursor
DEALLOCATE pub_crsr
In addition to being able to retrieve data from a cursor, you can perform positional
updates and deletes against the data contained in the cursor. When a modification
is made to a cursor, that modification automatically cascades to the cursor's data
source.
Syntax
UPDATE table_name
SET column_name1 = {expression1 | NULL | (select_statement)}
[, column_name2 = {expression2 | NULL | (select_statement)}...]
WHERE CURRENT OF cursor_name
DELETE FROM table_name
WHERE CURRENT OF cursor_name
table_name | The name of the table to UPDATE or DELETE. |
column_name | The name of the column to UPDATE. |
cursor_name | The cursor name. |
UPDATE publishers
SET pub_name = `XYZ publisher'
WHERE CURRENT OF pub_crsr
Example 2: Delete a Row in the publishers Table
This delete is based on the current row position in the cursor:
DELETE FROM publishers
WHERE CURRENT OF pub_crsr
The following two global variables can be used to monitor the status of a cursor: @@fetch_status and @@cursor_rows.
The @@fetch_status variable displays the status of a last FETCH command. Following are the possible values for @@fetch_status:
0 | Successful fetch |
-1 | The fetch failed or the fetch caused the cursor to go beyond the result set. |
-2 | The fetch row is missing from the data set. |
The following is an example of @@fetch_status:
while @@fetch_status = 0
...do some processing
The @@cursor_rows variable displays the number of rows in the cursor set. Use this variable after the cursor has been opened. Following are the possible values for @@cursor_rows:
-n | Cursor is currently being loaded with data. The number returned indicates the number of rows currently in the key result set; however, the number continues to increase as SQL Server processes the SELECT statement (this is known as asynchronous processing). |
n | Number of rows in the result set. |
0 | No matching rows in the result set. |
Now that you know something about cursor statements, positional updates, and global variables, the following cursor examples show you how all these components fit together.
This example shows how the different components of a cursor (DECLARE, OPEN, FETCH, and DEALLOCATE) are used to loop through the publishers table. The @@fetch_status global variable is referenced each time a FETCH is performed. Once the record pointer reaches the end of the result set, the @@fetch_status variable are equal to -1. This arrangement prevents the code inside the while @@fetch_status = 0 section from being executed.
/* suppress counts from being displayed */
SET NOCOUNT ON
/* declare a cursor that will contain the pub_id, pub_name columns */
/* from the publishers table */
declare pub_crsr cursor
for
select pub_id,pub_name
from publishers
/* open the cursor */
open pub_crsr
/* get the first row from the cursor */
fetch next from pub_crsr
/* loop through the rows in the cursor */
while @@fetch_status = 0
begin
/* get next row */
fetch next from pub_crsr
end
/* close the cursor *
deallocate pub_crs
Output
pub_id pub_name
0736 New Moon Books
0877 Binnet & Hardley
1389 Algodata Infosystems
1622 Five Lakes Publishing
1756 Ramona Publishers
9901 GGG&G
9952 Scootney Books
9999 Lucerne Publishing
This example displays object names and types for all user-defined objects in the pubs database. It uses two variables (@name and @type) and conditional logic to determine the object type.
/* suppress counts from being displayed */
SET NOCOUNT ON
/* declare variables */
declare @name varchar(30)
declare @type char(2)
/* declare a cursor that will contain a list of object */
/* names and object types */
declare object_list cursor
for
select name, type
from sysobjects
where type <> `S'
order by type
/* open the cursor */
open object_list
/* get the first row from the cursor */
fetch next from object_list into @name,@type
/* loop through the rows in the cursor */
while @@fetch_status = 0
begin
/* determine object type */
if @type = `C'
select `(CHECK constraint) ` + @name
if @type = `D'
select `(Default or DEFAULT constraint) ` + @name
if @type = `F'
select `(FOREIGN KEY constraint) ` + @name
if @type = `K'
select `(PRIMARY KEY or UNIQUE constraint) ` + @name
if @type = `L'
select `(Log) ` + @name
if @type = `P'
select `(Stored procedure) ` + @name
if @type = `R'
select `(Rule) ` + @name
if @type = `RF'
select `(Stored procedure for replication) ` + @name
if @type = `TR'
select `(Trigger) ` + @name
if @type = `U'
select `(User table) ` + @name
if @type = `V'
select `(View) ` + @name
if @type = `X'
select `(Extended stored procedure) ` + @name
/* get next table name */
fetch next from object_list into @name,@type
end
/* close the cursor */
deallocate object_list
Output
name type
(CHECK constraint) CK__authors__au_id__02DC7882
(CHECK constraint) CK__authors__zip__04C4C0F4
(CHECK constraint) CK__jobs__max_lvl__2719D8F8
(CHECK constraint) CK__jobs__min_lvl__2625B4BF
(CHECK constraint) CK__publisher__pub_i__089551D8
(CHECK constraint) CK_emp_id
(Default or DEFAULT constraint) DF__authors__phone__03D09CBB
(Default or DEFAULT constraint) DF__employee__hire_d__30A34332
(Default or DEFAULT constraint) DF__employee__job_id__2BDE8E15
(Default or DEFAULT constraint) DF__employee__job_lv__2DC6D687
(Default or DEFAULT constraint) DF__employee__pub_id__2EBAFAC0
(Default or DEFAULT constraint) DF__jobs__job_desc__25319086
(Default or DEFAULT constraint) DF__publisher__count__09897611
(Default or DEFAULT constraint) DF__titles__pubdate__0F424F67
(Default or DEFAULT constraint) DF__titles__type__0D5A06F5
(FOREIGN KEY constraint) FK__discounts__stor___2160FFA2
(FOREIGN KEY constraint) FK__employee__job_id__2CD2B24E
(FOREIGN KEY constraint) FK__employee__pub_id__2FAF1EF9
(FOREIGN KEY constraint) FK__pub_info__pub_id__3567F84F
(FOREIGN KEY constraint) FK__roysched__title___1E8492F7
(FOREIGN KEY constraint) FK__sales__stor_id__1AB40213
(FOREIGN KEY constraint) FK__sales__title_id__1BA8264C
(FOREIGN KEY constraint) FK__titleauth__au_id__1312E04B
(FOREIGN KEY constraint) FK__titleauth__title__14070484
(FOREIGN KEY constraint) FK__titles__pub_id__0E4E2B2E
(PRIMARY KEY or UNIQUE constraint) PK__jobs__job_id__243D6C4D
(PRIMARY KEY or UNIQUE constraint) PK_emp_id
(PRIMARY KEY or UNIQUE constraint) UPK_storeid
(PRIMARY KEY or UNIQUE constraint) UPKCL_auidind
(PRIMARY KEY or UNIQUE constraint) UPKCL_pubind
(PRIMARY KEY or UNIQUE constraint) UPKCL_pubinfo
(PRIMARY KEY or UNIQUE constraint) UPKCL_sales
(PRIMARY KEY or UNIQUE constraint) UPKCL_taind
(PRIMARY KEY or UNIQUE constraint) UPKCL_titleidind
(Stored procedure) byroyalty
(Stored procedure) reptq1
(Stored procedure) reptq2
(Stored procedure) reptq3
(Trigger) employee_insupd
(User table) authors
(User table) discounts
(User table) employee
(User table) jobs
(User table) pub_info
(User table) publishers
(User table) roysched
(User table) sales
(User table) stores
(User table) titleauthor
(User table) titles
(View) titleview
Example 3: Update Statistics for All Tables in a Database This example combines the EXEC command with a cursor to automatically update the statistics for all tables within a database.
/* declare variables */
declare @table_name varchar(30)
/* declare a cursor that will contain a list of table */
/* names to be updated */
declare idx_cursor cursor
for select distinct a.name
from sysobjects a,sysindexes b
where a.type = `U'
and a.id = b.id
and b.indid > 0
/* open the cursor */
open idx_cursor
/* get the first row from the cursor */
fetch next from idx_cursor into @table_name
/* loop through the rows in the cursor */
while @@fetch_status = 0
begin
/* issue UPDATE STATISTICS */
EXEC ("UPDATE STATISTICS " + @table_name)
/* get next table name */
fetch next from idx_cursor into @table_name
end
/* close the cursor */
deallocate idx_cursor
Stranger than Fiction!The SQL Server 6.x documentation incorrectly shows how to use the EXEC statement to execute a command at run time. In the example in the documentation, the concatenation symbol (+) was not included between the command and the variable.
Incorrect Syntax:
EXEC ("DROP TABLE " @tablename)Correct Syntax:
EXEC ("DROP TABLE " + @tablename)
Example 4: Positional Update This example looks at each row in the publishers table. If the pub_id column is equal to `1389', the pub_name column is updated to `XYZ publisher'.
/* suppress counts from being displayed */
SET NOCOUNT ON
/* declare variables */
declare @pub_id char(4),@pub_name varchar(40)
/* declare a cursor that will contain the pub_id, pub_name columns */
/* from the publishers table */
/* NOTE: for UPDATE clause allows position updates */
declare pub_crsr cursor
for
select pub_id,pub_name
from publishers
for UPDATE OF pub_id,pub_name
/* open the cursor */
open pub_crsr
/* get the first row from the cursor */
fetch next from pub_crsr into @pub_id, @pub_name
/* loop through the rows in the cursor */
while @@fetch_status = 0
begin
if @pub_id = `1389'
update publishers
set pub_name = `XYZ publisher'
where current of pub_crsr
/* get next row */
fetch next from pub_crsr into @pub_id, @pub_name
end
/* close the cursor */
deallocate pub_crsr
Example 5: Batch Run The following stored procedure and cursor example
allows you to schedule a single stored procedure with the SQL Server scheduler that
in turn executes any stored procedures in a table of batch procedures. The following
example was used for an organization that performed many different types of batch
processing using nightly stored procedures. The stored procedures were required to
run in a certain order, and the addition of new procedures was a common occurrence.
Instead of constantly scheduling the procedures, this example creates a single table,
Batch_Procedures, that holds the names of the stored procedures to execute
during the nightly batch run. It then creates a stored procedure called usp_Batch_Run
that executes each procedure in the Batch_Procedures table.
NOTE: The current limitations to this stored procedure (which you can easily modify) are as follows: usp_Batch_Run can execute only stored procedures in the same database as usp_Batch_Run. This limitation exists because the system table sysobjects is checked, as a security measure, to validate that the name in the Batch_Procedures table is an existing stored procedure. usp_Batch_Run does not support stored procedures with parameters.
The batch processing consists of one support table, one report table, and a stored procedure. The table schema for Batch_Procedures is as follows:
CREATE TABLE Batch_Procedures (
priority int,
procedure_name varchar (20),
description varchar (255)
)
CREATE UNIQUE CLUSTERED INDEX cidx_priority ON Batch_Procedures
( priority )
For example, if you have a stored procedure named usp_rollups that is the first procedure to execute in the batch, you would add the procedure to the table Batch_Procedures as follows:
INSERT Batch_Procedures
Values(0,"usp_rollups","First procedure of the batch")
The stored procedure usp_Batch_Run opens a cursor on the Batch_Procedures table and executes each procedure in the Batch_Procedures table. The syntax for the Batch_Run procedure is as follows:
CREATE PROCEDURE usp_Batch_Run AS
/*--------------------------------------------------------------
usp_Batch_Run
----------------------------------------------------------------
Description: Executes all the stored procedures that are
stored in the table Batch_Procedures. Results
from the batch run are stored in the table
Batch_Results. This procedure is schedule to run
via the task scheduler.
Parameters: None.
Status Returns: None.
Example: usp_Batch_Run
------------------------------------------------------------------
History:
Mark Spenik, SAMS - DBA Survival Guide - 2nd Edition
March 28, 1996
Initial Release.
----------------------------------------------------------------*/
Declare @status int, @procedure_name varchar(20)
Declare @priority int, @description varchar(255), @id int
Declare @IsProc int
/*
** Declare a cursor to retrieve each stored procedure listed
** in the Batch_Procedures table.
*/
declare batch_run_crsr cursor
For Select procedure_name,priority,description
from Batch_Procedures
Order By priority
/*
** Clear out the results table from the previous night's run.
*/
truncate table Batch_Results
/*
** Open the cursor to begin running the batch stored procedures.
*/
Open batch_run_crsr
if @@error != 0
goto Batch_Error
/*
** Get the first Row
*/
fetch next from batch_run_crsr
into @procedure_name, @priority, @description
While (@@fetch_status = 0) And (@@error = 0)
begin
/*
** Make sure it's a stored procedure
*/
select @IsProc = count(*)
from sysobjects
where id = object_id(@procedure_name)
and type = `P'
if @IsProc > 0
begin
/*
** First log the starting time in the batch results table.
*/
Insert Batch_Results
Values(getdate(), NULL, @procedure_name, @description, NULL)
/*
** Save identity value for the update.
*/
Select @id = @@identity
/*
** Execute the Stored Procedure
*/
Execute @status = @procedure_name
/*
** Update the results table.
*/
UPDATE Batch_Results
set end_time = getdate(),
status = @status
Where id = @id
END /* If IsProc > 0 */
/*
** Get the next procedure.
*/
fetch next from batch_run_crsr
into @procedure_name, @priority, @description
end /* While */
close batch_run_crsr
deallocate batch_run_crsr
return 0
/*
** Simple Error Exit
*/
Batch_Error:
RAISERROR (`Error executing stored procedure usp_Batch_Run',16,-1) return -100
The table Batch_Results is truncated every time the stored procedure usp_Batch_Run is executed. The Batch_Results table is used to log the status of the procedures that are executed during the stored procedure usp_Batch_Run. You can enhance the procedure by selecting all the rows from the Batch_Results table and use e-mail to notify users of the status of the batch run. The table schema for the Batch_Results table is as follows:
CREATE TABLE Batch_Results (
id int IDENTITY,
start_time datetime,
end_time datetime NULL,
proc_name varchar (20) NULL,
msg varchar ( 255 ) NULL,
status int NULL
)
CREATE UNIQUE CLUSTERED INDEX cidx_Id ON Batch_Results
( id )
Following are some important points to remember about SQL Server stored procedures:
Following are some important points to remember about cursors:
As a DBA, it is important that you understand how you can use stored procedures
and cursors to simplify your daily and weekly routines. If you understand what you
can and cannot do with stored procedures and cursors, you can be of great benefit
to your organization. You must also understand the concepts of compiled and
reusable but not reentrant if you want to help developers who are trying to
improve the performance of their applications.
To order books from QUE, call us at 800-716-0044
or 317-361-5400.
For comments or technical support for our books and software, select Talk to Us.
© 1997, QUE Corporation, an imprint of Macmillan Publishing USA, a Simon and Schuster
Company.