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.

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:

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:

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.

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:

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.

     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.

    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.

Temporary procedures are created the same way temporary tables are created—a prefix of one pound sign (#) creates a local temporary procedure that’s visible only to the current connection, while a prefix of two pound signs (##) creates a global temporary procedure that’s visible to all connections.

System procedures are procedures that reside in the master database and are prefixed with sp_. System procedures are executable from any database. When executed from a database other than master, the system procedure assumes the context of the database in which it’s running. So, for example, if it references the sysobjects table, which exists in every database, it will access the one in the database that’s current when it’s executed, not the master. Here’s an example of a simple system procedure:

This procedure lists the names and creation dates of the objects that match a mask.

Here’s an example that uses one of SQL Server’s own system stored procedures. Like the procedure above, it can be run from any database to retrieve info on that database:

Sp_spaceused queries various system tables to create the report it returns. Even though it resides in the master database, it automatically reflects the context of the current database because it’s a system procedure.

Note that you can trick system procedures into running in the context of any database (regardless of the current database) by prefixing them with the target database as though they resided in that database. Here’s an example:

Here, even though sp_spaceused resides in master, and despite the fact that the current database is pubs, sp_spaceused reports space utilization info for the Northwind database because we’ve prefixed its name with Northwind. Even though sp_spaceused doesn’t reside in Northwind, SQL Server correctly locates it in master and runs it within the Northwind database context.

A system procedure that’s created by a user is listed as a user object in Enterprise Manager. This is because the system bit of its status column in sysobjects (0xC0000000) isn’t set by default. You can change this by calling the undocumented procedure sp_MS_marksystemobject. The procedure takes one argument—the name of the object whose system bit you want to set. Several undocumented functions and DBCC command verbs do not work properly unless called from a system object (see Chapter 20, “Undocumented T-SQL,” for more information). You can determine whether an object’s system bit has been set via the OBJECTPROPERTY() function’s IsMSShipped property.

Extended procedures are routines that reside in DLLs (Dynamic Link Libraries) that look and work like regular stored procedures. They receive parameters and return results via the Open Data Services framework and are normally written in C or C++. They reside in the master database (you cannot create them elsewhere) and run within the SQL Server process space.

Note that there’s nothing about extended procedures that requires them to be written in C or C++, but if you intend to write them in another language, you’ll first have to complete the formidable task of translating the Microsoft-provided ODS header files into that language. I’ve personally written extended procedures using Delphi and a couple of other tools, so this can be done, but it’s not for the timid.

Another possibility for calling routines written in languages besides C/C++ is to create “wrapper” routines using a C++ compiler and the ODS headers and call your routines (which reside in some other DLL) from them. Then you get the best of both worlds—you create procedures in the language you prefer, and you’re not forced to translate a bevy of constants, function declarations, and the like to another language.

Note that, unlike SQL Server 6.5 and earlier, extended procedure names are not case sensitive. Prior to version 7.0, extended procedure calls had to match the case of the underlying routine as it existed in its DLL, regardless of the case-sensitivity setting on the server. With version 7.0 and later, the server will find the underlying routine regardless of the case used.

Calls to extended procedures do not work like system procedures. They aren’t automatically located in master when referenced from other databases, and they don’t assume the context of the current database when run. If you want to execute an extended procedure from a database other than master, you’ll have to qualify the reference (e.g., EXEC master..xp_cmdshell ’dir') fully.

A common technique of making extended procedures a bit handier is to wrap them in system stored procedures. This allows them to be called from any database without requiring the “master..” prefix. You see this in a number of SQL Server’s own routines—many undocumented extended procedures are wrapped within system stored procedures. Here’s an example of a wrapped call to an extended procedure:

All this procedure really does is clean up the parameters to be passed to the extended procedure xp_varbintohexstr before calling it. Because it’s a system procedure, it can be called from any database without referencing the extended procedure directly.

There are a number of system-supplied stored procedures that are neither true system procedures nor extended procedures—they’re implemented internally by the server itself. Examples of these include sp_executesql, sp_prepare, most of the sp_cursorXXXX routines, sp_reset_connection, etc. These routines have stubs in master..sysobjects, and are listed as extended procedures but are, in fact, implemented internally by the server, not within an external ODS-based DLL. You can’t list their source code because it’s part of the server itself, and you can’t trace into them with a T-SQL debugger because they’re not written in Transact-SQL.

The INSERT command supports calling a stored procedure in order to supply rows for insertion into a table. Here’s an example:

This is a handy way of trapping the output of a stored procedure in a table so that you can manipulate it or retain it for later use. Prior to the advent of cursor OUTPUT parameters, this was the only way to perform further work on a stored procedure’s result set within Transact-SQL.

Note that INSERT...EXEC works with extended procedures that return result sets as well. Here’s a simple example:

Output parameters allow values to be returned from stored procedures. These parameters can be integers, character strings, dates, and even cursors. Here’s an example:

Output parameters are identified with the OUTPUT keyword (which can be abbreviated as “OUT”). Notice the use of the OUT keyword in the procedure definition as well as in the EXEC parameter list. Both the procedure and its caller must specify which parameters are output parameters.

Cursor output parameters are a sensible means of returning a result set to a caller. Notice the use of the varying keyword with the cursor parameter in the procedure definition. This keyword is required with cursor parameters and indicates that the return value is nonscalar—that is, it returns more than a single value. Cursor parameters can only be output parameters, so the OUT keyword is required as well.

Procedures return result codes via the RETURN command. A return code of 0 indicates success, values –1 through –14 indicate different types of failures, and values –15 through –99 are reserved for future use. Table 15.1 lists the meaning of codes –1 through –14:

Table 15.1. Stock return codes and their meanings.

You can access a procedure’s return code by assigning it to an integer value, like this:

You can list a procedure’s parameters (which include its return code—considered parameter 0) using the undocumented procedure sp_procedure_params_rowset. Here’s an example:

(Results abridged)

By their very nature, automatic variables (what the Books Online now call “functions”) are usually accessed from within stored procedures. This makes most of them relevant in some way to stored procedures. However, a few of them are more relevant to stored procedure use than the others. Table 15.2 summarizes them.

Table 15.2. Stored procedure–related automatic variables.

No discussion of stored procedures would be complete without covering control-of-flow language statements. These are referred to as “flow control” or “control-of-flow” statements because they control the flow of execution through a stored procedure or batch. Transact-SQL flow control language statements include IF...ELSE, WHILE, GOTO, RETURN, WAITFOR, BREAK, CONTINUE, and BEGIN..END. Without repeating what’s already covered quite adequately by the Books Online, here’s a simple procedure that illustrates all of them:

Stored procedures report errors via return codes and the RAISERROR command. RAISERROR doesn’t change the flow of the procedure, it merely displays an error message (optionally writing it to the SQL Server error log and the NT application event log) and sets the @@ERROR automatic variable. RAISERROR can reference predefined error messages that reside in the sysmessages table (you create these with sp_addmessage), or you can supply it with a custom message string. If you supply a custom message during the call to RAISERROR, the error number is set to 50,000. RAISERROR can format messages similarly to the C printf() function, allowing you to supply your own arguments for the messages it displays.

RAISERROR allows both a severity and a state to be specified with each message. Severity values less than 16 produce informational messages in the system event log (when logged), a severity of 16 produces a warning message in the event log, and severity values greater than 16 produce error messages in the event log. Severity values up through 18 can be specified by any user; severity values 19–25 are reserved for members of the sysadmin role and require the use of the WITH LOG option. Note that severity values over 20 are considered fatal and cause the client connection to be terminated.

State is an informational value that you can use to indicate state information to your front-end application—it has no predefined meaning to SQL Server. Raising an error with a state of 127 will cause the ISQL and OSQL utilities to set the operating system ERRORLEVEL value to the error number returned by RAISERROR. Note that, unlike releases prior to 7.0, the ISQL utility no longer exits immediately when a state of 127 is used—it merely sets ERRORLEVEL; OSQL, by contrast, exits immediately. So if we have this SQL batch:

and we execute it from this operating system command batch:

here’s what happens:

This is handy for exiting a command batch immediately without causing undue alarm or generating unnecessary entries in the system event log. Though you could raise a message with a high severity level to terminate the connection, that creates log entries and potentially raises a red flag over something that’s completely normal—aborting a batch before processing it completely. And though you could also abort the batch with the EXIT command, the operating system ERRORLEVEL wouldn’t be set, so you’d have no way of knowing why the batch exited.

RAISERROR supports a handful of options that affect its behavior. The WITH LOG option copies the error message to the NT event log (assuming SQL Server is running on Windows NT) and the SQL Server error log regardless of whether the message was defined using the with_log option of sp_addmessage. The WITH NOWAIT option causes the message to be returned immediately to the client. The WITH SETERROR option forces the automatic @@ERROR variable to return the last error number raised, regardless of the severity of the error message.

The system procedure sp_addmessage is used to add messages to the sysmessages table that RAISERROR can then use. User messages should have error numbers of 50,000 or higher. The chief advantage of using SQL Server’s system messages facility is that it’s language independent. Because you specify a language with each message you add, you can have several messages with the same error number but with different language indicators. Then, based on the language setting the user chooses when installing SQL Server, the appropriate message will be displayed when your code calls RAISERROR.

Because RAISERROR can display a message and set the @@ERROR variable in one fell swoop, it’s sometimes used for tasks other than displaying error messages. Its printf()-like formatting ability makes it ideal for formatting strings other than error messages. Here’s an example that features RAISERROR used to list a table:

Of course, the obligatory “Msg...” lines would be a bit of an annoyance, but you could strip these out in your front-end application if you decided to use this approach.

Stored procedures can be nested up to 32 levels deep. The @@NESTLEVEL automatic variable indicates the level of nesting at any given time. A nesting level of 0 is returned at the command batch level, 1 within each stored procedure called from level 0 (and from first-level triggers), 2 for each proc called from level 1, and so forth. Objects (including temporary tables) and cursors created within a stored procedure are visible to all objects it calls. Objects and cursors created at level 0 are visible to all objects.

Transact-SQL supports recursion. Recursion can be defined as a method of solving a problem wherein the solution is arrived at by repetitively applying it to subsets of the problem. An obvious use of recursion is in creating parsers and performing numeric computations that lend themselves to repetitive evaluation by the same processing logic. Here’s a an example that features a stored procedure that calculates the factorial of a number:

The first thing this procedure does is create a decimal-based user-defined data type that matches the @@MAX_PRECISION automatic variable. This allows the procedure to use as large a number as the server can handle. Next, the procedure checks to make sure it has been passed a valid number for which to compute a factorial. It then recursively calls itself to perform the computation. As you can see, with the default maximum numeric precision of 28, SQL Server can handle numbers in excess of 400 septillion! ¹

Autostart procedures have lots of practical uses. You can use them to perform start-up processes and other administrative work. You can use them to load commonly used procedures into the procedure cache with each server boot. You use the sp_procoption stored procedure to flag a stored procedure as an autostart routine, like so:

Some notes about autostart procedures:

You can encrypt the source code that’s stored in syscomments for a stored procedure, view, or trigger using the WITH ENCRYPTION option when you create the object. This prevents users from viewing your source code with tools such as Enterprise Manager, but it also thwarts stored procedure debuggers, like the one included with the Enterprise Edition of Visual Studio. Encrypted objects have the third bit of the texttype column in syscomments set.

Note that once you’ve encrypted an object, there’s no supported way of decrypting it. You can’t view it, nor can members of the sysadmin role or anyone else.

A trigger is a special type of stored procedure that executes when a specified DML operation (an INSERT, DELETE, or UPDATE or any combination of them) occurs. Triggers are constructed via the CREATE TRIGGER command and are attached to tables. When its host table is dropped, so is the trigger.

Most of the details of stored procedure programming apply equally well to triggers. In fact, since you can call a stored procedure from a trigger, you can effectively do anything in a trigger that a stored procedure can do. One thing that triggers don’t normally do is return result sets. Most front ends have no way of handling trigger-generated result sets, so you just don’t see it in production code. Note that SQL Server doesn’t permit triggers to return result codes.

Triggers fire once per statement, not per row, regardless of the number of rows changed by a given DML statement. You can set up as many triggers as you want (well, up to 2 billion per database, anyway) for a table—triggers associated with the same DML statement will fire in succession in no particular order.

DRI (declarative referential integrity) constraints have precedence over triggers. This means that a violation of a DRI constraint by a DML command will prevent triggers from executing.

Inside triggers, you can check which columns are being updated by a DML operation via the UPDATE() and COLUMNS_UPDATE() functions. The UPDATE() function returns true or false based on whether the value of a specified column is being set (regardless of whether it’s actually changing). COLUMNS_UPDATED() returns a bitmap representing which columns are being set.

Triggers can cause other triggers to fire if the nested triggers option has been enabled with sp_configure. Triggers can fire themselves recursively if the recursive triggers database option has been enabled. The @@NESTLEVEL automatic variable returns 1 within a first-level trigger, 2 within one it causes to fire, 3 for any it causes to fire, and so forth.

When a user transaction is not active, a trigger and the DML operation that fired it are considered a single transaction. When a trigger generates a fatal error or executes a ROLLBACK TRANSACTION, the currently active transaction is rolled back and the current command batch is canceled.

SQL Server exposes special logical tables for use by triggers: the inserted and deleted tables. For INSERT operations, the inserted table lists the row(s) about to be appended to the table. For DELETE operations, the deleted table lists the row(s) about to be removed from the table. For UPDATE operations, the deleted table lists the old version of the row(s) about to be updated, and the inserted table lists the new version. You can query these tables to allow or prevent database modifications based on the columns or data the operations are attempting to modify. Rolling back the current transaction is normally the way that triggers are aborted since SQL Server’s Transact-SQL doesn’t support a ROLLBACK TRIGGER command (à la Sybase). Note that you can’t modify these logical tables—they’re for inspection only.

Nonlogged operations (operations that do not generate row modification log records) do not fire triggers. So, for example, even though TRUNCATE TABLE deletes all the rows in a table, those row deletions aren’t logged individually and therefore do not fire any delete triggers that may have been defined for the table.

You can disable a trigger via the ALTER TABLE...DISABLE TRIGGER command. Disabled triggers can be reenabled using ALTER TABLE...ENABLE TRIGGER. Here are a few examples:

Triggers fire just after the work has been completed by the DML statement but before it has been committed to the database. A DML statement’s execution plan branches to any triggers it fires just before returning. If the trigger permits the operation to proceed, and if no user transaction is present, any changes made by the DML statement are then committed to the database.

Here are a few trigger examples:

Some general trigger notes:

The Enterprise Edition of Visual Studio, as well as various third-party tools, allows Transact-SQL stored procedures to be debugged. This means that you can step into stored procedures called from Visual Studio projects such as VB and VC++ applications. You can set breakpoints, establish watches, and generally do what debuggers are designed to do—debug code.

The interface by which this occurs is known as the SQL Server Debug Interface, or SDI for short. It was originally introduced with SQL Server 6.5 and has now been completely integrated with Visual Studio.

Some notes on debugging Transact-SQL with the SDI:

In this chapter, you explored many of the nuances and idiosyncrasies of building stored procedures and triggers. You learned how to construct user as well as system procedures and how to pass parameters to and from the procedures you create. You became familiar with some of the internals of the stored procedure execution process, and you learned how triggers work. You became acquainted with debugging stored procedures, and you learned about fringe elements of stored procedure creation such as encryption and execution plan recompilation.