Stored Procedures : Part 1

A stored procedure is a batch of SQL that's stored permanently on the server and compiled when used.

It's not compiled in the sense of being translated to machine code or even Java byte codes—it's

Pseudocompiled to speed execution. You create stored procedures using the Transact-SQL CREATE

PROCEDURE command.

All that really happens when you create a procedure is the insertion of its source code into the

Syscomments system  table. The procedure isn't compiled until it's executed for the first time (and in

certain other circumstances— see the following section, "Internals," for more information). Despite the

name, syscomments stores far more than comments—it's the repository for the source code for stored

procedures, views, triggers, rules, and defaults. If you delete the source code for an object from

syscomments, the object will no longer be accessible.

You can list the source code to a procedure, view, trigger, rule, or default using the sp_helptext system

procedure. If the object is not encrypted, sp_helptext will list its source, formatted similarly to the way

you entered it. Here's an example:

EXEC sp_helptext 'sp_hexstring'

Text

--------------------------------------------------------------------------------

CREATE PROC dbo.sp_hexstring @int varchar(10)=NULL, @hexstring varchar(30)=NULL

OUT

/*

Object: sp_hexstring

Description: Return an integer as a hexadecimal string

Usage: sp_hexstring @int=Integer to convert, @hexstring=OUTPUT parm to receive

hex string

Returns: (None)

Created by: Ken Henderson. Email: khen@khen.com

Version: 1.0

Example: sp_hexstring 23, @myhex OUT

Created: 1999-08-02. Last changed: 1999-08-15.

*/

AS

IF (@int IS NULL) OR (@int = '/?') GOTO Help

DECLARE @i int, @vb varbinary(30)

SELECT @i=CAST(@int as int), @vb=CAST(@i as varbinary)

EXEC master..xp_varbintohexstr @vb, @hexstring OUT

RETURN 0

Help:

EXEC sp_usage @objectname='sp_hexstring',

@desc='Return an integer as a hexadecimal string',

@parameters='@int=Integer to convert, @hexstring=OUTPUT parm to receive hex

string',

@example='sp_hexstring "23", @myhex OUT',

@author='Ken Henderson',

@email='khen@khen.com',

@version='1', @revision='0',

@datecreated='19990802', @datelastchanged='19990815'

RETURN -1

Stored Procedure Advantages

There are several advantages to using stored procedures; here are a few of them:

• They allow business rules and policies to be encapsulated and changed in one place.

• They allow sharing of application logic by different applications.

• They can facilitate data modification, ensuring that all applications update data consistently.

• They can simplify parameterized queries, easily facilitating running the same query repetitively with

different sets of parameters.

• Autostart procedures can automate startup routines, executing each time the server is cycled.

• They can modularize an application, organizing it into manageable pieces.

• They can provide security mechanisms, allowing users controlled access to database objects they

could not otherwise use.

• They can reduce network bandwidth use by greatly lessening the amount of Transact-SQL code that

must traverse the network in order to accomplish tasks.

• Since their execution plans are retained by the server for reuse, they can improve application

performance considerably.

Internals

There are four major steps involved with using stored procedures:

1. Creation—where you initially create the procedure with CREATEPROC

2. User execution—where you execute it with EXEC

3. Compilation—where the server compiles and optimizes the procedure during an EXEC

4. Server execution—where the server runs its compiled execution plan during an EXEC

Creation

The creation step is where you use the CREATE PROCEDURE command to construct the procedure on the

server. Each time you successfully create a new procedure, its name and other vital information are

recorded in sysobjects, and its source code is stored in syscomments. Objects referenced by the

procedure are not resolved until you execute it.

User Execution

The first time you execute a newly created procedure (or the server recompiles it), it's read from

syscomments, and its object references are resolved. During this process, the command processor

constructs what's known as a sequence tree or query tree that will be passed to the query optimizer for

compilation and optimization.

Compilation

Once the query tree has been successfully created, the SQL Server query optimizer compiles the entire

batch, optimizes it, and checks access privileges.

During the optimization phase, the optimizer scans the query tree and develops what it believes is the

optimal plan for accessing the data the procedure is after.

The following criteria are considered during this step:

• The presence of the GROUP BY, ORDER BY, and UNION clauses

• The amount of data the procedure will retrieve

• The use of joins to link tables together

• The characteristics of the indexes built over referenced tables

• The degree of data distribution in each index's key columns

• The use of comparison operators and values in WHERE and HAVING clauses within the procedure

An execution plan is the result of this process, and it's placed in the procedure cache when the optimizer

finishes building it. This execution plan consists of the following:

• The steps required to carry out the work of the stored procedure

• The steps necessary to enforce constraints

• The steps needed to branch to any triggers red by the stored procedure

Execution plans in SQL Server 7.0 and later are reentrant and read-only. This differs from previous

releases, where each connection received its own copy of the execution plan for a given procedure.

Server Execution

The execution phase is where the execution plan is processed sequentially and each step is dispatched to

An appropriate internal manager process. There are a number of internal managers— the DDL and DML

managers, the transaction manager, the ODSOLE manager (for processing the OLE automation procedures

such as sp_OAcreate), the stored procedure manager, the utility manager, the T-SQL manager, etc. These

managers are called repeatedly until all steps in the execution plan have been processed.

Execution plans are never stored on disk. The only portion of the stored procedure that's stored

Permanently is its source code (in syscomments). Since they're kept in memory, cycling the server

disposes of all current execution plans (as does the undocumented DBCC FREEPROCCACHE() command).

SQL Server will automatically recreate a procedure's execution plan when:

• The procedure's execution environment differs significantly from its creation environment (see the

following section, "Environmental Concerns," for more information).

• The sysobjects schema_ver column changes for any of the objects the procedure references. The

schema_ver and base_schema_ver columns are updated any time the schema information for a

table changes. This includes column additions and deletions, data type changes, constraint additions

and deletions, as well as rule and default bindings.

• The statistics have changed for any of the objects the procedure references.

• An index that was referenced by the procedure's execution plan is dropped.

• A copy of the procedure's execution plan is not available in the cache. Execution plans are removed

from the cache to make room for new plans using an LRU (least recently used) algorithm.

Additionally, you can force a procedure's execution plan to be recompiled using these three methods:

1. Creating the procedure using the WITH RECOMPILE option (and then executing it)

2. Executing the procedure using the WITH RECOMPILE option

3. Flagging any of the tables the procedure references with the sp_recompile procedure (sp_recompile

merely updates sysobjects' schema_ver column) and then executing it

A nifty way to load execution plans into the cache at system startup is to execute them via an autostart

procedure. Rather than execute each procedure itself as an autostart routine, you should call the

procedures you want to load into the cache from a single autostart procedure in order to conserve

execution threads (each autostart routine gets its own thread).

Once an execution plan is in the cache, subsequent calls to the procedure can reuse the plan without

rebuilding the query tree or recompiling the plan. This eliminates two of the three steps that occur when

you execute a stored procedure and is the chief performance advantage stored procedures give you over

plain SQL batches.

Creating Stored Procedures

You create stored procedures using the CREATE PROCEDURE command; you alter them with ALTER

PROCEDURE. The advantage to using ALTER PROC rather than CREATE PROC to change a stored

procedure is that it preserves access permissions, whereas CREATE PROC doesn't. A key difference

between them is that ALTER PROC requires the use of the same encryption and recompile options as the

original CREATE PROC. Other than that, the semantics of using the two commands are exactly the same.

A procedure can contain any valid Transact-SQL command except these: CREATE DEFAULT, CREATE

PROC, CREATE RULE, CREATE SCHEMA, CREATE TRIGGER, and CREATE VIEW. Procedures can

create databases, tables, and indexes but not other procedures, defaults, rules, schemas, triggers, or

views.

NOTE

Note GO is not a Transact-SQL command. It's a command batch terminator, which is to say, it tells

tools like Query Analyzer and OSQL where one batch of SQL ends and another begins. As such,

it's never allowed within a stored procedure— attempting this simply terminates the procedure. One

rather odd aspect of the fact that GO is not a Transact-SQL command comes into play with

comments. You can't comment out GO using the /**/comments. If GO is the leftmost item on its line,

it will terminate the command batch regardless of the comment markers. Since this will prevent the

closing comment marker from being reached, you'll get an error message about a missing end

comment marker. The solution? Use the comment style, delete the GO altogether, or remove

its"G." To execute CREATE PROC you must be a member of the sysadmin role, the db_owner role, or the

db_ddladmin_role. You can also execute CREATE PROC if you've been explicitly granted permission by a

member of either the sysadmin or db_owner role.The maximum stored procedure size is the lesser of

65,536 * the network packet size (which defaults to 4096 bytes) and 250 megabytes. The maximum

number of parameters a procedure may receive is 1024.

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 on. You can place this comment block after the

CREATE PROC statement itself (but before the rest of the procedure) in order to ensure that it's

stored in syscomments and is visible from tools like Enterprise Manager that can access stored

procedure source code directly via syscomments. Here's a system procedure that generates

comment headers for you:

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,

@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 object-creation SQL scripts

Usage: sp_object_script_comments @objectname="ObjectName", @desc="Description

of object",@parameters="param1[,param2…]"

Created by: Ken Henderson. Email: khen@khen.com

Version: 3.1

Example usage: sp_object_script_comments @objectname="sp_who", @desc="Returns

a list of currently running jobs", @parameters=[@loginname]

Created: 1992-04-03. Last changed: 1999-07-01 01:13:00.

*/

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,

@email=@email,

@version=@version, @revision=@revision,

@datecreated=@datecreated, @datelastchanged=@datelastchanged

PRINT CHAR(13)+'*/'

RETURN 0

Help:

EXEC 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',

@email='khen@khen.com',

@version='3', @revision='1',

@datecreated='19920403', @datelastchanged='19990701'

RETURN -1y

This procedure generates comment header information for a stored procedure by calling the sp_usage

procedure detailed below. It can be executed from any database by any procedure.

• Allow an optional single parameter to be passed into every procedure that tells the caller how to use

the procedure (e.g., '/?'). You can place this usage information at the end of the procedure in order to

keep it from crowding your display and to locate it consistently from procedure to procedure. The best

way to do this is to set up and call a separate procedure whose whole purpose is to report usage

information. Here's a script that creates the sp_usage procedure that's used throughout this book for

that very purpose:

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,

@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"]

[, @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)

Version: 3.1

Example: sp_usage @objectname="sp_who", @desc="Returns

a list of currently running jobs", @parameters=[@loginname]

Created: 1992-04-03. Last changed: 1999-07-01.

*/

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

IF (@author IS NOT NULL)

PRINT CHAR(13)+'Created by: '+@author+'. Email: '+@email

IF (@version IS NOT NULL)

PRINT CHAR(13)+'Version: '+@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

+'. Last changed: '+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 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"]

[, @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',

@email='khen@khen.com',

@version='3', @revision='1',

@datecreated='4/3/92', @datelastchanged='7/1/99'

RETURN -1

You can call sp_usage to report usage info for any procedure. In fact, sp_usage calls itself to do just that.

(That's the source of the 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.) Note the

use of a GOTO label to place the usage info at the end of the procedure. Since Transact-SQL doesn't

support subroutines, this is unfortunately necessary. It allows code at the start of the procedure to check

for invalid parameter values and quickly jump to the usage routine if necessary.

• Set any environment options (QUOTED_IDENTIFIER, ANSI_DEFAULTS, etc.) that materially affect

the procedure early in it. It's a good practice to set them

• immediately on entrance to the procedure so that their presence is obvious to other developers.

• Avoid situations where the owner of a stored procedure and the owner of its referenced tables differ.

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 a layer of obfuscation to the database

that nobody needs. While perhaps plausible during development, it's definitely something to avoid on

production servers. Allow database users besides dbo to own objects only in very special

circumstances.

• Don't use the sp_ prex for anything but system procedures that reside in the master database. Don't

create procedures in user databases with the sp_ prex, and don't create nonsystem procedures in

master.

• For procedures that must be created in a specific database (e.g., system procedures), include USE

dbname, where dbname is the name of the target database, at the top of the script that creates the

procedure. This ensures that the procedure winds up where you want it and alleviates having to

remember to change the current database in your query tool before executing the script.

• Keep stored procedures as simple and modular as possible. Each stored procedure should

accomplish a single task or a small group of closely related tasks.

• Use SET NOCOUNT ON to minimize network traffic from stored procedures. As a rule, it should be

the first statement in every stored procedure you create. (Note that SET NOCOUNT ON can cause

problems with some applications—e.g., some versions of Microsoft Access.)

• Create a procedure using the WITH ENCRYPTION option if you want to hide its source code from

users. Don't delete it from syscomments—doing so will render the procedure unable to execute, and

you'll have to drop and recreate it.