Tuesday, November 25, 2014

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'
CREATE PROC dbo.sp_hexstring @int varchar(10)=NULL, @hexstring varchar(30)=NULL
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.
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
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
@example='sp_hexstring "23", @myhex OUT',
@author='Ken Henderson',
@version='1', @revision='0',
@datecreated='19990802', @datelastchanged='19990815'

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.

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

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.

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
create databases, tables, and indexes but not other procedures, defaults, rules, schemas, triggers, or

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
IF OBJECT_ID('dbo.sp_object_script_comments') IS NOT NULL
DROP PROC dbo.sp_object_script_comments
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.
IF (@objectname+@desc) IS NULL GOTO Help
PRINT '/*'
EXEC sp_usage @objectname=@objectname,
@version=@version, @revision=@revision,
@datecreated=@datecreated, @datelastchanged=@datelastchanged
PRINT CHAR(13)+'*/'
EXEC sp_usage @objectname='sp_object_script_comments',
@desc='Generates comment headers for SQL scripts',
@parameters='@objectname="ObjectName", @desc="Description of
@example='sp_object_script_comments @objectname="sp_who",
a list of currently running jobs", @parameters=[@loginname]',
@author='Ken Henderson',
@version='3', @revision='1',
@datecreated='19920403', @datelastchanged='19990701'
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
IF OBJECT_ID('dbo.sp_usage') IS NOT NULL
DROP PROC 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.
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
PRINT CHAR(13)+'Usage: '+@objectname+' '+@parameters
PRINT CHAR(13)+'Returns: '+@returns
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+'.'
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',
@version='3', @revision='1',
@datecreated='4/3/92', @datelastchanged='7/1/99'
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
• 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

• 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.

No comments:

Post a Comment