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.
No comments:
Post a Comment