This chapter is from the book
This chapter is from the book
This chapter is from the book
Creating a Stored Procedure
As I've said, you use the Transact-SQL CREATE PROCEDURE command to create stored procedures. All that really happens when you create a procedure is that its syntax is checked and its source code is inserted into the syscomments system table. Generally, object names referenced by a procedure aren't resolved until it's executed. In SQL Server parlance, this is known as deferred name resolution.
"Syscomments" is a misnomer. The table doesn't store comments per se; it stores source code. The name is a vestige from the pre-7.0 days and was so named because it stored the optional source code to stored procedures (and other objects), whereas sysprocedures stored a pseudo-compiled version (a kind of normalized query tree) of the procedures themselves. This is no longer true, and the sysprocedures table no longer exists. Syscomments is now the sole repository for stored procedures, views, triggers, user-defined functions (UDFs), rules, and defaults. If you remove an object's source code from syscomments, you can no longer use that object.
Deferred Name Resolution and an Interesting Exception
Before we go further, it's worth mentioning that there's an interesting exception to SQL Server's default deferred name resolution behavior. Run the code in Listing 1–2 in Query Analyzer:
Listing 1–2 SQL Server doesn't allow you to include more than one CREATE TABLE statement for a given temporary table in the same stored procedure.
CREATE PROC testp @var int AS IF @var=1 CREATE TABLE #temp (k1 int identity, c1 int) ELSE CREATE TABLE #temp (k1 int identity, c1 varchar(2)) INSERT #temp DEFAULT VALUES SELECT c1 FROM #temp GO
The syntax contained in the stored procedure is seemingly valid, yet we get this message when we run it:
Server: Msg 2714, Level 16, State 1, Procedure testp, Line 6 There is already an object named '#temp' in the database.
Why? Obviously @var can't be both equal to one and not equal to one, right? To get a little closer to the answer, change the temporary table reference to a permanent table, like the one shown in Listing 1–3.
Listing 1–3 Changing the table to a permanent table works around the temporary table limitation.
CREATE PROC testp @var int AS IF @var=1 CREATE TABLE tempdb..temp (k1 int identity, c1 int) ELSE CREATE TABLE tempdb..temp (k1 int identity, c1 varchar(2)) INSERT #temp DEFAULT VALUES SELECT c1 FROM #temp GO
This procedure is created without error. What's happening here? Why does SQL Server care whether the table created is a temporary or a permanent table? And why does it matter now—before the procedure is even executed and before the value of @var can be known?
What appears to be happening is that SQL Server resolves CREATE TABLE references to temporary tables before inserting the procedure into syscomments—an apparent vestige from the pre-7.0 days when object references were resolved when a procedure was first created. The same limitation applies to variable declarations and, therefore, to the table data type. You can't declare a variable more than once in a single stored procedure, even if the declarations reside in mutually exclusive units of code. This differs from how permanent tables are handled, and is the reason the code in Listing 1–3 runs without error. It appears that, beginning with SQL Server 7.0, deferred name resolution was enabled for permanent tables, but not for temporary ones. Whatever the case, you can't execute code like that shown in Listing 1–2, so here's a workaround (Listing 1–4):
Listing 1–4 Including one CREATE TABLE statement, but two sets of ALTER TABLE statements, works around the problem.
CREATE PROC testp @var int
AS
CREATE TABLE #temp (k1 int identity)
IF @var=1
ALTER TABLE #temp ADD c1 int
ELSE
ALTER TABLE #temp ADD c1 varchar(2)
INSERT #temp DEFAULT VALUES
EXEC('SELECT c1 FROM #temp')
GO
This technique creates the table just once, then alters it to match the Data Definition Language (DDL) specification (spec) we want to end up with. Note the use of EXEC() to select the column we added with ALTER TABLE. The use of dynamic SQL is necessary because the newly added column isn't immediately visible to the procedure that added it. We're forced to create and execute an ad hoc query string to access it by name. (Note that you can reference the column indirectly—for example, through SELECT * or via an ordinal value in an ORDER BY clause, just not by name).
Another disadvantage of this approach is that it mixes DDL (the CREATE and ALTER statements) and Data Modification Language (DML; the INSERT and SELECT). Because of this, the procedure's execution plan must be recompiled when the INSERT is encountered (the temporary table's schema information (info) has changed since the original execution plan was formulated). Any stored procedure that creates a temporary table, then processes it further, will cause a plan recompile because the table's schema info did not exist when the execution plan was first created; however, the procedure in Listing 1–4 causes an additional recompile to occur because it alters this schema, then processes the table further. Particularly with large procedures in high-throughput environments, this can cause performance problems as well as blocking and concurrency issues because a compile lock is taken out on the stored procedure while the execution plan is being recompiled. Listing 1–5 presents a workaround that doesn't require the use of dynamic T-SQL:
Listing 1–5 A workaround for the temporary table creation problem.
CREATE PROCEDURE testp4 AS INSERT #temp DEFAULT VALUES SELECT c1 FROM #temp GO CREATE PROC testp3 AS CREATE TABLE #temp (k1 int identity, c1 varchar(2)) EXEC dbo.testp4 GO CREATE PROC testp2 AS CREATE TABLE #temp (k1 int identity, c1 int) EXEC dbo.testp4 GO CREATE PROC testp @var int AS IF @var=1 EXEC dbo.testp2 ELSE EXEC dbo.testp3 GO
Although this technique alleviates the need for EXEC(), it also forces us to completely reorganize the stored procedure. In fact, we're forced to break the original procedure into four separate routines and call the fourth one redundantly from the second and third routines. Why? First, instead of having two CREATE TABLE statements for the same temporary table in one procedure—which, as we've discovered, isn't supported—we moved each CREATE TABLE to its own procedure. Second, because a temporary table is automatically dropped as soon as it goes out of scope, we can't simply create it, then return to the top-level routine and add rows to it or SELECT from it. We have to do that either in one of the procedures that created it or in a common routine that they call. We chose the latter, so procedures two and three call a fourth routine that takes care of inserting the row into the temporary table and selecting the c1 column from it. (Because objects created in a procedure are visible to the procedures it calls, the fourth routine can "see" the table created by its caller.) This approach works, but is far from optimal. Think about how complex this would get for a really large procedure. Breaking it into multiple, distinct pieces may not be practical. Still, it avoids the necessity of having to create and execute an ad hoc T-SQL string and should generally perform better than that approach.
Listing a Stored Procedure
Assuming the object is not encrypted, you can list the source code to a procedure, view, trigger, UDF, rule, or default object using the sp_helptext system procedure. An example is included in Listing 1–6:
Listing 1–6 sp_helptext lists the source for a stored procedure.
EXEC dbo.sp_helptext 'ListCustomersByCity' Text --------------------------------------------------------------------------- CREATE PROCEDURE dbo.ListCustomersByCity @Country nvarchar(30)='%' AS SELECT City, COUNT(*) AS NumberOfCustomers FROM Customers WHERE Country LIKE @Country GROUP BY City
Permissions and Limitations
Only members of the sysadmin, db_owner, or db_ddladmin role (or those explicitly granted CREATE PROC permission by a member of the appropriate role) can execute CREATE PROCEDURE.
The maximum stored procedure size is 128MB. The maximum number of parameters a procedure may receive is 1,024.
Creation Tips
Include a comment header with each procedure that identifies its author, purpose, creation date and revision history, the parameters it receives, and so forth. A common technique is to place this comment block either immediately before or just after the CREATE PROC statement itself (but before the rest of the procedure) to ensure that it's stored in syscomments and can be viewed from tools like Enterprise Manager and Query Analyzer's Object Browser. The system stored procedure that follows, sp_object_script_comments, generates comment headers for stored procedures, views, and similar objects (Listing 1–7):
Listing 1–7 You can use sp_object_script_comments to generate stored procedure comment headers.
USE master
GO
IF OBJECT_ID('dbo.sp_object_script_comments') IS NOT NULL
DROP PROC dbo.sp_object_script_comments
GO
CREATE PROCEDURE dbo.sp_object_script_comments
-- Required parameters
@objectname sysname=NULL,
@desc sysname=NULL,
-- Optional parameters
@parameters varchar(8000)=NULL,
@example varchar(8000)=NULL,
@author sysname=NULL,
@workfile sysname='', -- Force workfile to be generated
@email sysname='(none)',
@version sysname=NULL,
@revision sysname='0',
@datecreated smalldatetime=NULL,
@datelastchanged smalldatetime=NULL
/*
Object: sp_object_script_comments
Description: Generates comment headers for SQL scripts
Usage: sp_object_script_comments @objectname='ObjectName', @desc='Description of
object",@parameters='param1[,param2...]'
Returns: (None)
$Workfile: sp_object_script_comments.sql $
$Author: Khen $. Email: khen@khen.com
$Revision: 1 $
Example: sp_object_script_comments @objectname='sp_who', @desc='Returns a list of
currently running jobs', @parameters=[@loginname]
Created: 1992-04-03. $Modtime: 1/4/01 8:35p $.
*/
AS
IF (@objectname+@desc) IS NULL GOTO Help
PRINT '/*'
PRINT CHAR(13)
EXEC sp_usage @objectname=@objectname,
@desc=@desc,
@parameters=@parameters,
@example=@example,
@author=@author,
@workfile=@workfile,
@email=@email,
@version=@version, @revision=@revision,
@datecreated=@datecreated, @datelastchanged=@datelastchanged
PRINT CHAR(13)+'*/'
RETURN 0
Help:
EXEC dbo.sp_usage @objectname='sp_object_script_comments',
@desc='Generates comment headers for SQL scripts',
@parameters='@objectname=''ObjectName'',
@desc=''Description of object",@parameters=''param1[,param2...]''',
@example='sp_object_script_comments @objectname=''sp_who'',
@desc=''Returns a list of currently running jobs'',
@parameters=[@loginname]',
@author='Ken Henderson',
@workfile='sp_object_script_comments.sql',
@email='khen@khen.com',
@version='3', @revision='1',
@datecreated='19920403', @datelastchanged='19990701'
RETURN -1
GO
EXEC dbo.sp_object_script_comments
This procedure generates stored procedure comment headers by calling the sp_usage procedure included later in the chapter. It can be executed from any database by any procedure. To use sp_object_script_comments, simply pass it the required parameters, and it will create a fully usable comment block that identifies a procedure or other type of object and spells out its usage and key background info. You can copy this block of text and paste it into the header of the routine itself and—voila!—you've got a nicely formatted, informative comment block for your code.
In shops with lots of stored procedure code, it's common to locate each stored procedure in its own script and to store each script in a version control or source code management system. Many of these systems support special tags (these are known as keywords in Visual SourceSafe [VSS], the source code management system that I use) that you can embed in T-SQL comments. Through these tags, you allow the source code management system to automatically insert revision information, the name of the person who last changed the file, the date and time of the last change, and so on. Because the tags are embedded in comments, there's no danger that these changes will break your code. Basically, you're just allowing the system to take care of some of the housekeeping normally associated with managing source code. Many of the stored procedures listed in this book include tags recognized by VSS in their headers (these tags begin and end with $). See Chapter 4 for more information.
Allow the passing of a single help parameter such as '/?'—or no parameters—to return an informational message telling the caller how to use the procedure. Place the section that generates this usage information at the end of the procedure to keep it out of the way and to locate it consistently from procedure to procedure. An ideal way to do this is to set up and call a separate procedure that accepts parameters indicating usage information and returns it in a uniform format. Here's a stored procedure that does just that (Listing 1–8):
Listing 1–8 You can use sp_usage to generate stored procedure usage info.
USE master
GO
IF OBJECT_ID('dbo.sp_usage') IS NOT NULL
DROP PROC dbo.sp_usage
GO
CREATE PROCEDURE dbo.sp_usage
-- Required parameters
@objectname sysname=NULL,
@desc sysname=NULL,
-- Optional parameters
@parameters varchar(8000)=NULL,
@returns varchar(8000)='(None)',
@example varchar(8000)=NULL,
@workfile sysname=NULL,
@author sysname=NULL,
@email sysname='(none)',
@version sysname=NULL,
@revision sysname='0',
@datecreated smalldatetime=NULL,
@datelastchanged smalldatetime=NULL
/*
Object: sp_usage
Description: Provides usage information for stored procedures and descriptions of
other types of objects
Usage: sp_usage @objectname='ObjectName', @desc='Description of object'
[, @parameters='param1,param2...']
[, @example='Example of usage']
[, @workfile='File name of script']
[, @author='Object author']
[, @email='Author email']
[, @version='Version number or info']
[, @revision='Revision number or info']
[, @datecreated='Date created']
[, @datelastchanged='Date last changed']
Returns: (None)
$Workfile: sp_usage.sql $
$Author: Khen $. Email: khen@khen.com
$Revision: 7 $
Example: sp_usage @objectname='sp_who', @desc='Returns a list of currently running
jobs', @parameters=[@loginname]
Created: 1992-04-03. $Modtime: 1/04/01 8:38p $.
*/
AS
SET NOCOUNT ON
IF (@objectname+@desc IS NULL) GOTO Help
PRINT 'Object: '+@objectname
PRINT 'Description: '+@desc
IF (OBJECTPROPERTY(OBJECT_ID(@objectname),'IsProcedure')=1)
OR (OBJECTPROPERTY(OBJECT_ID(@objectname),'IsExtendedProc')=1)
OR (OBJECTPROPERTY(OBJECT_ID(@objectname),'IsReplProc')=1)
OR (LOWER(LEFT(@objectname,3))='sp_') BEGIN -- Special handling for system
procedures
PRINT CHAR(13)+'Usage: '+@objectname+' '+@parameters
PRINT CHAR(13)+'Returns: '+@returns
END
-- $NoKeywords: $ -- Prevents the keywords below from being expanded in VSS
IF (@workfile IS NOT NULL)
PRINT CHAR(13)+'$Workfile: '+@workfile+' $'
IF (@author IS NOT NULL)
PRINT CHAR(13)+'$Author: '+@author+' $. Email: '+@email
IF (@version IS NOT NULL)
PRINT CHAR(13)+'$Revision: '+@version+'.'+@revision+' $'
IF (@example IS NOT NULL)
PRINT CHAR(13)+'Example: '+@example
IF (@datecreated IS NOT NULL) BEGIN -- Crop time if it's midnight
DECLARE @datefmt varchar(8000), @dc varchar(30), @lc varchar(30)
SET @dc=CONVERT(varchar(30), @datecreated, 120)
SET @lc=CONVERT(varchar(30), @datelastchanged, 120)
PRINT CHAR(13)+'Created: '+CASE
DATEDIFF(ss,CONVERT(char(8),@datecreated,108),'00:00:00') WHEN 0 THEN
LEFT(@dc,10) ELSE @dc END
+'. $Modtime: '+CASE DATEDIFF(ss,CONVERT(char(8),@datelastchanged,108),'00:00:00')
WHEN 0 THEN LEFT(@lc,10) ELSE @lc END+' $.'
END
RETURN 0
Help:
EXEC dbo.sp_usage @objectname='sp_usage', -- Recursive call
@desc='Provides usage information for stored procedures and descriptions
of other types of objects',
@parameters='@objectname=''ObjectName'', @desc=''Description of object''
[, @parameters=''param1,param2...'']
[, @example=''Example of usage'']
[, @workfile=''File name of script'']
[, @author=''Object author'']
[, @email=''Author email'']
[, @version=''Version number or info'']
[, @revision=''Revision number or info'']
[, @datecreated=''Date created'']
[, @datelastchanged=''Date last changed'']',
@example='sp_usage @objectname=''sp_who'',
@desc=''Returns a list of currently running jobs'',
@parameters=[@loginname]',
@author='Ken Henderson',
@workfile='sp_usage.sql',
@email='khen@khen.com',
@version='3', @revision='1',
@datecreated='4/3/92', @datelastchanged='7/1/99'
RETURN -1
GO
EXEC dbo.sp_usage
By passing in the appropriate parameters, you can use sp_usage to report usage info for any procedure. Sp_usage even calls itself for that very purpose (that's why we receive the warning message: "Cannot add rows to sysdepends for the current stored procedure because it depends on the missing object 'sp_usage.' The stored procedure will still be created."). Because Transact-SQL doesn't support subroutines, sp_usage uses a GOTO label to place the help message at the end of the procedure. This approach allows code at the start of the procedure to check for invalid parameter values and to jump quickly to the usage routine if necessary.
Set the QUOTED_IDENTIFIER and ANSI_NULLS options before you execute CREATE PROCEDURE (in its own command batch) because they're reset to the values they had at the time the procedure was created when it's executed (their values are stored in the status column of the procedure's row in sysobjects). This change lasts only for the duration of the procedure; afterward, they're restored to whatever they were before you executed the procedure. Setting QUOTED_IDENTIFIER or ANSI_NULLS inside a stored procedure has no effect on the execution of the stored procedure. To see how this works, run the code in Listing 1–9 in Query Analyzer:
Listing 1–9 SET ANSI_NULLS has no effect inside a stored procedure.
USE tempdb GO SET ANSI_NULLS ON GO CREATE PROC testn AS SET ANSI_NULLS OFF DECLARE @var int SET @var=NULL SELECT * FROM Northwind..Customers WHERE @var=NULL GO EXEC testn
(Results abridged)
CustomerID CompanyName ContactName ---------- ------------------------- ---------------- (0 row(s) affected)
If ANSI_NULLS is actually off at the time of the SELECT, as the SET command inside the procedure specifies, the SELECT should return all the rows in the Northwind Customers table. As you can see, this is not what happens. Now change the SET ANSI_NULLS command that precedes the CREATE PROCEDURE to turn ANSI null handling OFF, and rerun the procedure. You should see all the rows in the Customers table listed.
Set environmental options (e.g., NOCOUNT, LOCK_TIMEOUT, and so on) that materially affect the procedure early on. It's a good practice to set them at the very start of the procedure so that they stand out to other developers.
Avoid broken ownership chains when dealing with stored procedures and the objects they reference. Try to ensure that the owner of a stored procedure and the owner of the objects it references are the same. The best way to do this is by specifying the dbo user as the owner of every object you create. Having multiple objects with the same name but different owners adds an unnecessary layer of indirection to the database that's almost always more trouble than it's worth. While perhaps useful during the development phase of a project, it's definitely something you should avoid on production servers.
When used within a stored procedure, certain commands require the objects they reference to be owner qualified (an object reference is said to be owner qualified when the object name is prefixed with the name of the owner and a period) if the procedure is to be executed by users other than the owner. These commands are
- CREATE TABLE
- ALTER TABLE
- DROP TABLE
- TRUNCATE TABLE
- CREATE INDEX
- DROP INDEX
- UPDATE STATISTICS
- All DBCC commands
Don't use the sp_ prefix for anything but system procedures. Because of the confusion it can cause, avoid creating procedures in user databases with the sp_ prefix. Also, don't create nonsystem procedures in the master database. If a procedure is not a system procedure, it's likely that you don't need to put it in the master database in the first place.
Include USE dbname at the top of creation scripts for procedures that must reside in a specific database. This ensures that they end up where they belong and alleviates having to remember to set the current database context before executing the script.
Keep each stored procedure as simple and modular as possible. Ideally, a stored procedure will accomplish a single task or a small group of closely related tasks.
As a rule, SET NOCOUNT ON should be the first statement in every stored procedure you create because it minimizes network traffic between SQL Server and client applications. Setting NOCOUNT on disables DONE_IN_PROC messages—the messages SQL Server normally sends to the client indicating the number of rows affected by a T-SQL statement. Because these messages are very rarely used, eliminating them conserves network bandwidth without really giving up any functionality and can speed up applications considerably. Note that you can disable DONE_IN_PROC messages for the entire server via a trace flag (3640) and for a particular user session via the sp_configure 'user options' command. (In rare circumstances, disabling DONE_IN_PROC messages can cause problems with some applications—for example, some older versions of Microsoft Access and certain ill-behaved OLEDB providers).
Create a procedure using the WITH ENCRYPTION option if you want to keep its source code from being viewable by users. Don't delete it from syscomments. Doing so will render the procedure inaccessible and you'll have to drop and recreate it.