ptg
904
CHAPTER 28 Creating and Managing Stored Procedures
Returning Procedure Status
Most programming languages are able to pass a status code to the caller of a function or
subroutine. A value of 0 generally indicates that the execution was successful. SQL Server
stored procedures are no exception.
SQL Server automatically generates an integer status value of 0 after successful completion
of a stored procedure. If SQL Server detects a system error, a status value between -1 and -
99 is returned. You can use the RETURN statement to explicitly pass a status value less than
-99 or greater than 0. The calling batch or procedure can set up a local variable to retrieve
and check the return status.
In Listing 28.16, the stored procedure returns the year-to-date sales for a given title as a
result set. If the title does not exist, to avoid returning an empty result set, the procedure
returns the status value
-101. In the calling batch or stored procedure, you need to create
a variable to hold the return value. The variable name is passed the EXECUTE keyword and
the procedure name as shown in Listing 28.16.
LISTING 28.16 Returning a Status Code from a Stored Procedure
IF EXISTS ( SELECT * FROM sys.procedures
WHERE schema_id = schema_id(‘dbo’)
AND name = N’ytd_sales2’)
DROP PROCEDURE dbo.ytd_sales2
GO
Create the procedure
CREATE PROC ytd_sales2 @title varchar(80)
AS
IF NOT EXISTS (SELECT * FROM titles WHERE title = @title)
RETURN -101
SELECT ytd_sales
FROM titles

WHERE title = @title
RETURN
GO
Execute the procedure
DECLARE @status int
EXEC @status = ytd_sales2 ‘Life without Fear’
IF @status = -101
PRINT ‘No title with that name found.’
go
ytd_sales

111
Download from www.wowebook.com
ptg
905
Debugging Stored Procedures Using SQL Server Management Studio
28
Execute the procedure
DECLARE @status int
EXEC @status = ytd_sales2 ‘Life without Beer’
IF @status = -101
PRINT ‘No title with that name found.’
go
No title with that name found.
Return values can also be passed back and captured by client applications developed in
ADO, ODBC, OLE DB, and so on.
Debugging Stored Procedures Using SQL Server
Management Studio
One of the great tools available in the SQL Server 2000 Query Analyzer, the built-in SQL
Debugger, was left out of SQL Server Management Studio (SSMS) in SQL Server 2005.

Fortunately, SQL Server 2008 brings the T-SQL debugger back to SSMS.
The Transact-SQL debugger in SQL Server Management Studio enables you to step through
Transact-SQL scripts, stored procedures, triggers, and functions as they are running. The
Transact-SQL debugger allows you to do the following:
. Step through the Transact-SQL statements in the Query Editor line by line or set
breakpoints to stop at specific lines.
. Step into or over Transact-SQL stored procedures, functions, or triggers run by the
code in the query editor window.
. Watch the values assigned to variables and observe system objects such as the call
stack and threads.
If you want to run the T-SQL Debugger, the query editor window must be connected to
SQL Server as a member of the
sysadmin server role.
NOTE
Debugging of T-SQL code should be done only on a test or development server, not on a
production server. Debugging sessions can often run for long periods of time while you
are investigating the operations of your Transact-SQL statements. If the code being
debugged involves a multistatement transaction, locks acquired by the session could be
held for extended periods while the code is paused in the debugger until the debugging
session is ended or the transaction committed or rolled back. This could lead to exten-
sive locking contention or blocking for other applications accessing production data.
Download from www.wowebook.com
ptg
906
CHAPTER 28 Creating and Managing Stored Procedures
You start the debugger in a query editor window by either clicking the Debug button on
the Query toolbar or by clicking Start Debugging on the Debug menu, as shown in Figure
28.8.
When the query editor window enters debug mode, the debugger initially stops on the
first line of code in the stored procedure, as shown in Figure 28.9. You can then set any

breakpoints and run to the breakpoints or step through the procedure code one line at a
time. You can press F10 to step through the code one line at a time. If the SQL code
invokes a stored procedure or function, or a DML statement invokes a trigger, you can
press F11 to step into the called routine. If you step into a routine in the T-SQL Debugger,
SQL Server Management Studio opens a new query editor window populated with the
source code for the routine, places the window into debug mode, and then pauses execu-
tion on the first statement in the routine. You can then step through or set breakpoints in
the code for that routine.
Located near the bottom of the debugger window are some useful information windows.
The first group of windows is the Locals/Watch window, which displays the contents of
local variables or any watch expressions you have defined. The Locals window displays
the current values in all the local variables within the current scope. You can also modify
the values of the variables in the Locals window to test various scenarios or to adjust data
values so the code executes differently. To modify the value of a variable, right-click the
row and select Edit Value.
In the four Watch windows, you can add variables or expressions whose values you want
to watch, such as the global variables
@@NESTLEVEL, @@FETCH_STATUS, or @@ROWCOUNT. To
add an expression to a Watch window, you can either select Add Watch in the
QuickWatch dialog box or enter the name of the expression in the Name column of an
FIGURE 28.8 Invoking the T-SQL debugger in SSMS.
Download from www.wowebook.com
ptg
907
Debugging Stored Procedures Using SQL Server Management Studio
28
FIGURE 28.9 Debugging a T-SQL stored procedure in SSMS.
empty row in a Watch window. The Watch windows, like the other tabbed windows in the
debugger, can be set as docked or floating windows, allowing you to view multiple
windows simultaneously (Figure 28.9 shows an example of the Watch1 window set as a

floating window).
The second group of windows is the Call Stack, Breakpoints, Output, and Results and
Messages windows. The Call Stack window displays the current execution location and
also displays information about how execution passed from the original query editor
window to the current execution location through any other functions, procedures, or
triggers. The Breakpoints window lets you view information about the breakpoints you
have set. From this window, you can also jump to the source code where the breakpoint is
set or disable or delete the breakpoint. The Output window displays various messages and
program data, including system messages from the debugger. The Results and Messages
tabs on the query editor window display the results of previously executed Transact-SQL
statements within the debugging session. The query editor window stays in debug mode
until either the last statement in the query editor window is executed or you manually
stop debugging. You can stop debugging, along with any further statement execution,
using any one of the following methods:
. On the Debug menu, click Stop Debugging.
. On the Debug toolbar, click the Stop Debugging button.
. On the Query menu, click Cancel Executing Query.
Download from www.wowebook.com
ptg
908
CHAPTER 28 Creating and Managing Stored Procedures
. On the Query toolbar, click the Cancel Executing Query button.
If you want to stop debugging but allow the remaining Transact-SQL statements to run to
completion, click Detach All on the Debug menu.
Using System Stored Procedures
A system stored procedure is a stored procedure that has some special characteristics.
These procedures, created when SQL Server is installed or upgraded, are generally used to
administer SQL Server. They shield a DBA from accessing the system catalogs directly.
Some system stored procedures are used to present information from the system catalog,
and others modify the system catalogs.

NOTE
System stored procedures seem to have fallen out of favor with Microsoft. Most of
them have been listed as deprecated features in SQL Server 2008 and been replaced
with T-SQL commands, or the information provided by system stored procedures is now
available via the catalog views and dynamic management views. Many of the current
system stored procedures may be removed in future versions of Microsoft SQL Server,
so it is recommended that you avoid using many of the system stored procedures in
any of your development work and modify any code currently using system stored pro-
cedures to use the alternatives.
Although many of the system stored procedures have been deprecated and are not as criti-
cal to administering SQL Server as they once were, it is still a good idea to be familiar with
the basic system stored procedures. There are currently around 400 documented system
stored procedures in SQL Server 2008, so it would be a tough job to learn the names and
syntax for all of them. The total number of system stored procedures is more than 1,400.
Some of the undocumented stored procedures are called by other procedures, and others
are called from SSMS or other SQL Server tools and utility programs.
The following attributes characterize a system stored procedure:
. The stored procedure name begins with
sp_.
. The stored procedure resides in the Resource database.
. The procedure is defined in the sys schema.
These attributes make the procedure global, which means you can execute the procedure
from any database without qualifying the database name. The procedure executes within
the current database context.
Although system stored procedures reside in the
Resource database, they also run in any
database context when fully qualified with a database name, regardless of the current
database context. For instance,
sp_helpfile shows information about the files configured
for the current database. In the following example, when not qualified, sp_helpfile

Download from www.wowebook.com
ptg
909
Using System Stored Procedures
28
returns file information for the master database, and when qualified with bigpubs2008 ,
it returns file information for the bigpubs2008 database:
exec sp_helpfile
go
name fileid filename filegroup
size maxsize growth usage


master 1 C:\MSSQL2008\MSSQL10.SQL2008UNLEASHED\MSSQL\DATA\master.mdf PRIMARY
4096 KB Unlimited 10% data only
mastlog 2 C:\MSSQL2008\MSSQL10.SQL2008UNLEASHED\MSSQL\DATA\mastlog.ldf NULL
512 KB Unlimited 10% log only
exec bigpubs2008 sp_helpfile
go
name fileid filename filegroup size
maxsize growth usage


bigpubs2008 1 E:\MSSQL2008\DATA\bigpubs2008.mdf PRIMARY 214912 KB
Unlimited 10% data only
bigpubs2008_log 2 E:\MSSQL2008\DATA\bigpubs2008_log.LDF NULL 504 KB
Unlimited 10% log only
Table 28.1 describes the categories of system stored procedures.
TABLE 28.1 System Stored Procedure Categories
Category Description

Catalog stored procedures Used to implement ODBC data dictionary functions and isolate
ODBC applications from changes to underlying system tables.
Cursor stored procedures Used to implement cursor variable functionality.
Database engine stored
procedures
Used for general maintenance of the SQL Server Database Engine.
Database mail stored
procedures
Used to perform email operations from within an instance of SQL
Server.
Database maintenance
plan procedures
Used to set up core database maintenance tasks.
Distributed queries stored
procedures
Used to link remote servers and manage distributed queries.
Download from www.wowebook.com
ptg
910
CHAPTER 28 Creating and Managing Stored Procedures
Some of the more useful system stored procedures are listed in Table 28.2.
TABLE 28.1 System Stored Procedure Categories
Category Description
Full-text search stored
procedures
Used to implement and query full-text indexes.
Log shipping stored
procedures
Used to configure, modify, and monitor log shipping configurations.
Automation stored

procedures
Allow OLE automation objects to be used within a T-SQL batch.
Notification services stored
procedures
Used to manage SQL Server 2008 Notification Services.
Replication stored
procedures
Used to manage replication.
Security stored procedures Used to manage security, such as login IDs, usernames, and so on.
SQL Server Profiler stored
procedures
Used by SQL Server Profiler to monitor performance and activity.
SQL Server Agent stored
procedures
Used by SQL Server Agent to manage scheduled and event-driven
activities.
Web task stored
procedures
Used for creating web pages.
XML stored procedures Used for XML text management.
General extended stored
procedures
Provide an interface from an instance of SQL Server to external
programs for various maintenance activities (for example,
xp_sqlmaint)
TABLE 28.2 Useful System Stored Procedures
Procedure Name Description
sp_who and sp_who2
Return information about current connec-
tions to SQL Server.

sp_help [object_name]
Lists the objects in a database or returns
information about a specified object.
sp_helpdb
Returns a list of databases or information
about a specified database.
sp_configure
Lists or changes configuration settings.
Download from www.wowebook.com
ptg
911
Startup Procedures
28
Startup Procedures
A SQL Server administrator can create stored procedures that are marked for execution
automatically whenever SQL Server starts. They are often referred to as startup procedures.
Startup procedures are useful for performing housekeeping-type tasks or starting up a
background process when SQL Server starts. Some possible uses for startup procedures
include the following:
. Automatically perform system or maintenance tasks in
tempdb, such as creating a
global temporary table.
. Enable custom SQL Server Profiler traces automatically whenever SQL Server is
running. (For more information on SQL Server Profiler traces, see Chapter 6.)
. Automatically start other external processes on the SQL Server machine, using
xp_cmdshell. (Using xp_cmdshell is discussed in the section “Using Extended Stored
Procedures,” in Chapter 44.)
. Prime the data cache with the contents of your critical, frequently used tables.
. Prime the plan cache by executing procedures or functions you want to have com-
piled and cached before applications start using them.

To create a startup procedure, you log in as a system administrator and create the proce-
dure in the
master database. Then you set the procedure startup option to true by using
sp_procoption:
sp_procoption procedure_name, startup, true
If you no longer want the procedure to run at startup, remove the startup option by
executing the same procedure and changing the value to false.
By default, a startup procedure runs in the context of the system administrator, but it can
use SETUSER to impersonate another account, if necessary. If you need to reference objects
in other databases from within a startup procedure, you need to fully qualify the object
with the appropriate database and owner names.
Startup procedures are launched asynchronously; that is, SQL Server doesn’t wait for them
to complete before continuing with additional startup tasks. This allows a startup proce-
dure to execute in a loop for the duration of the SQL Server process, or it allows several
startup procedures to be launched simultaneously. While a startup procedure is running, it
runs as a separate worker thread.
Download from www.wowebook.com
ptg
912
CHAPTER 28 Creating and Managing Stored Procedures
TIP
If you need to execute a series of stored procedures in sequence during startup, you
can nest the stored procedure calls within a single startup procedure. This approach
consumes only a single worker thread.
Any error messages or print statements generated by a startup procedure are written to the
SQL Server error log. For example, consider the following whimsical but utterly useless
startup procedure:
use master
go
create procedure good_morning

as
print ‘Good morning, Dave’
return
go
sp_procoption good_morning, startup, true
go
When SQL Server is restarted, the following entries would be displayed in the error log:
2009-06-12 13:21:00.04 spid5s Recovery is complete. This is an
informational message only. No user action is required.
2009-06-12 13:21:00.15 spid5s Launched startup procedure ‘good_morning’.
2009-06-12 13:21:00.15 spid51s Good morning, Dave
Any result sets generated by a startup procedure vanish into the infamous bit bucket. If
you need to return result sets from a startup procedure, the procedure should be written
to insert the results into a table. The table needs to be a permanent table and not a tempo-
rary table because a temporary table would be automatically dropped when the startup
procedure finished executing.
The following example is a startup procedure that could preload all tables within the
Sales and Purchasing schemas in the AdventureWorks database into data cache memory
on SQL Server startup:
use master
go
create procedure prime_cache
as
declare @tablename varchar(128),
@schemaname varchar(128)
Download from www.wowebook.com
ptg
913
Startup Procedures
28

declare c1 cursor for
select s.name, o.name
from AdventureWorks.sys.objects o
join AdventureWorks.sys.schemas s
on o.schema_id = s.schema_id
where type = ‘U’
and s.name in (‘Sales’, ‘Purchasing’)
open c1
fetch c1 into @schemaname, @tablename
while @@fetch_status = 0
begin
print ‘Loading ‘’’ + @schemaname + ‘.’ + @tablename + ‘’’ into data cache’
exec (‘select * from AdventureWorks.’ + @schemaname + ‘.’ + @tablename)
fetch c1 into @schemaname, @tablename
end
close c1
deallocate c1
return
go
sp_procoption prime_cache, startup, true
go
The error log output from this startup procedure would be similar to the following:
2009-06-15 19:39:18.970 spid7s Launched startup procedure ‘prime_cache’.
2009-06-15 19:39:20.550 spid30s Loading ‘Sales.Store’ into data cache
2009-06-15 19:39:20.870 spid30s Loading ‘Sales.StoreContact’ into data cache
2009-06-15 19:39:20.870 spid30s Loading ‘Purchasing.ProductVendor’ into data
cache
2009-06-15 19:39:20.950 spid30s Loading ‘Purchasing.Vendor’ into data cache
2009-06-15 19:39:21.010 spid30s Loading ‘Purchasing.PurchaseOrderDetail’ into
data cache

2009-06-15 19:39:21.140 spid30s Loading ‘Purchasing.VendorAddress’ into data
cache
2009-06-15 19:39:21.150 spid30s Loading ‘Purchasing.VendorContact’ into data
cache
2009-06-15 19:39:21.160 spid30s Loading ‘Purchasing.PurchaseOrderHeader’ into
data cache
2009-06-15 19:39:21.220 spid30s Loading ‘Sales.ContactCreditCard’ into data
cache
2009-06-15 19:39:21.310 spid30s Loading ‘Sales.CountryRegionCurrency’ into
data cache
2009-06-15 19:39:21.420 spid30s Loading ‘Sales.CreditCard’ into data cache
2009-06-15 19:39:21.540 spid30s Loading ‘Sales.Currency’ into data cache
Download from www.wowebook.com