How It Works

The solution example makes a parameter optional by specifying a default value as follows:

@UpperFlag VARCHAR(1) = 'x'

It’s now possible to invoke the procedure by passing only an account number. The default value takes effect in that case, and the person’s name is returned unchanged, without being forced to uppercase.

It’s common to specify default parameters at the end of the parameter list. Doing so makes it easier to invoke a procedure in an ad hoc manner from SQL Management Studio. Also, many are used to this convention. However, you can define optional parameters earlier in the list, and the next recipe shows how.

Problem

The parameter you want to make optional precedes one that is mandatory. You are thus unable to skip that parameter even though you’ve specified a default value for it.

Solution

Invoke your procedure using named notation rather than positional notation. In doing so, you can name the parameter that you do want to pass, and it won’t matter where in the list that parameter occurs.

For example, begin with the following version of the procedure from Recipe 17-4. In this version, the UpperFlag parameter comes first.

CREATE PROCEDURE dbo.LookupByAccount2
(@UpperFlag CHAR(1) = 'x',
@AccountNumber VARCHAR(10))
AS

       SELECT     CASE UPPER(@UpperFlag)
                    WHEN 'U' THEN UPPER(FirstName)
                    ELSE FirstName
                  END AS FirstName,
                  CASE UPPER(@UpperFlag)
                    WHEN 'U' THEN UPPER(LastName)
                    ELSE LastName
                  END AS LastName
       FROM       Person.Person
       WHERE      BusinessEntityID IN (SELECT CustomerID
                                       FROM   Sales.Customer
                                       WHERE  AccountNumber = @AccountNumber) ;

Using named notation, you can pass just the account number as follows:

EXEC LookupByAccount2 @AccountNumber = 'AW00000019';

You can use the DEFAULT keyword to make it explicit that you are accepting a default parameter value for @UpperFlag.

EXEC LookupByAccount2 @AccountNumber = 'AW00000019', @UpperFlag = DEFAULT;

Using named notation, you can specify the parameter values in any order.

How It Works

It’s common to pass parameters using positional notation. Named notation takes some extra typing but in return can be a bit more self-documenting. That’s because each procedure invocation names all the parameters, helping you remember later what the associated parameter values represent.

Using named notation also allows you to specify parameters in any order. That ability allows you to skip parameters having default values, no matter where those parameters occur in the list. Don’t try to mix the two notations. SQL Server requires that you choose one or the other. Specify all parameters by name or all by position. Don’t try to mix the two approaches.

Problem

You are writing a stored procedure. You want to return values to the code calling the procedure.

Solution

Specify some parameters as OUTPUT parameters. The following example creates a stored procedure that returns the list of departments for a specific group. In addition to returning the list of departments, an OUTPUT parameter is defined to return the number of departments found for the specific group.

CREATE PROCEDURE dbo.EL_Department
       @GroupName NVARCHAR(50),
       @DeptCount INT OUTPUT
AS
       SELECT Name
       FROM   HumanResources.Department
       WHERE  GroupName = @GroupName
       ORDER BY Name;
       SELECT @DeptCount = @@ROWCOUNT;

Now you can define a local variable to hold the output and invoke the procedure. Here’s an example:

DECLARE @DeptCount INT;
EXEC dbo.SEL_Department 'Executive General and Administration',
  @DeptCount OUTPUT;
PRINT @DeptCount;

The query in the procedure generates the row set. The PRINT command displays the row count value passed back through the @DeptCount variable. The query in this example returns the following five rows:

Name
--------------------------
Executive
Facilities and Maintenance
Finance
Human Resources
Information Services

Next, the stored procedure uses the PRINT statement to return the count of rows. If you’re executing the query ad hoc using Management Studio, you will see the value 5 on the Messages tab if you are viewing results as a grid and at the bottom of the Results tab if you are viewing results as text. Here’s an example:

5

How It Works

The solution begins by creating a stored procedure with a defined parameter called @DeptCount, followed by the data type and OUTPUT keyword:

@DeptCount INT OUTPUT

The stored procedure executes the query and then stores the row count in the parameter.

SELECT @DeptCount = @@ROWCOUNT

The invoking code creates the following variable to pass as the output parameter:

DECLARE @DeptCount INT

The EXEC statement must also specify that a parameter is an output parameter. Do that by following the passed value with the OUTPUT keyword, as in the following:

EXEC dbo.SEL_Department 'Executive General and Administration',
  @DeptCount OUTPUT;

You can use OUTPUT parameters as an alternative or additional method for returning information back to the caller of the stored procedure. If you’re using OUTPUT only to communicate information back to the calling application, it’s usually just as easy to create a second result set containing the information you need. This is because .NET applications, for example, can easily consume the multiple result sets that are returned from a stored procedure. The technique of using OUTPUT parameters versus using an additional result set to return information is often just a matter of preference.

Problem

You have an existing stored procedure and want to change its behavior.

Solution

Redefine the procedure using the ALTER PROCEDURE command. You can change everything but the original stored procedure name. The syntax is almost identical to CREATE PROCEDURE.

The following example modifies the stored procedure created in the previous recipe in order to return the number of departments returned by the query as a separate result set instead of via an OUTPUT parameter.

ALTER PROCEDURE dbo.SEL_Department
  @GroupName NVARCHAR(50)
AS
  SELECT   Name
  FROM     HumanResources.Department
  WHERE    GroupName = @GroupName
  ORDER BY Name;
  SELECT   @@ROWCOUNT AS DepartmentCount;

You may now execute the stored procedure as follows, and two result sets are returned:

EXEC dbo.SEL_Department 'Research and Development';

Name
------------------------
Engineering
Research and Development
Tool Design
DepartmentCount
---------------
        3

How It Works

ALTER PROCEDURE is used to modify the definition of an existing stored procedure, in this case both removing a parameter and adding a second result set. You can change everything but the procedure name. Using ALTER PROCEDURE also preserves any existing permissions on the stored procedure. If you drop and re-create the procedure, you’ll need to re-grant permissions. Using ALTER PROCEDURE avoids the need for that tedium.

Problem

You are no longer using a stored procedure and want to remove it from your database.

Solution

Drop the stored procedure from the database using the DROP PROCEDURE command. Here’s an example:

DROP PROCEDURE dbo.SEL_Department;

How It Works

Once a stored procedure is dropped, its definition is removed from the database’s system tables. Any cached query execution plans are also removed for that stored procedure. Code references to the stored procedure by other procedures or triggers will fail upon execution once the stored procedure has been dropped.

Problem

You want to execute some code every time a particular instance is started. For example, you might want to document start-up times or clear out work tables on each restart.

Solution

Invoke the sp_procoption system stored procedure to designate a procedure that you write to execute automatically upon instance start-up. In the example to follow, a stored procedure is set to execute automatically whenever SQL Server is started. First, set the database context to the master database (which is the only place that start-up stored procedures can be placed).

USE master;

Next, create a start-up logging table. Do this because the procedure this recipe creates as an example writes to this table. Here is the creation statement:

CREATE TABLE dbo.SQLStartupLog
       (
        SQLStartupLogID INT IDENTITY(1, 1)
                            NOT NULL
                            PRIMARY KEY,
        StartupDateTime DATETIME NOT NULL
       );

Then create a stored procedure to insert a value into the new table.

CREATE PROCEDURE dbo.INS_TrackSQLStartups
AS
       INSERT dbo.SQLStartupLog
        (StartupDateTime)
       VALUES (GETDATE());

Finally, invoke sp_procoption to set this new procedure to execute when the SQL Server service restarts.

EXEC sp_procoption @ProcName = 'INS_TrackSQLStartups',
  @OptionName = 'startup', @OptionValue = 'true';

From now on, starting the instance triggers execution of the stored procedure, which in turn inserts a row into the table to log the start-up event.

How It Works

This recipe creates a new table in the master database to track SQL Server start-ups. A stored procedure is also created in the master database to insert a row into the table with the current date and time of execution.

The stored procedure must be created in the master database; otherwise, you’ll see the following error message when trying to use sp_procoption:

Msg 15398, Level 11, State 1, Procedure sp_procoption, Line 73 Only objects in the master database owned by dbo can have the startup setting changed.

To disable the stored procedure, execute the following command:

EXEC sp_procoption @ProcName = 'INS_TrackSQLStartups',
  @OptionName = 'startup', @OptionValue = 'false'

Setting @OptionValue to false disables the start-up procedure.

Problem

You want to view the definition for a stored procedure so that you can ascertain exactly how that procedure operates.

Solution

From an ad hoc session, it’s often easiest to execute sp_helptext. Here’s an example:

EXEC sp_helptext 'LookupByAccount';

Your results will be in the form of a CREATE PROCEDURE statement:

Text
------------------------------------------------------------------------------
CREATE PROCEDURE dbo.LookupByAccount
(@AccountNumber VARCHAR(10),
 @UpperFlag VARCHAR(1) = 'x')
AS
       SELECT     CASE UPPER(@UpperFlag)
                    WHEN 'U' THEN UPPER(FirstName)
                    ELSE FirstName
                  END AS FirstName,
                  CASE UPPER(@UpperFlag)
                    WHEN 'U' THEN UPPER(LastName)
                    ELSE LastName
                  END AS LastName
       FROM       Person.Person
       WHERE      BusinessEntityID IN (SELECT CustomerID
                                       FROM   Sales.Customer
                                       WHERE  AccountNumber = @AccountNumber) ;

From code, you may prefer to query sys.sql_modules and related catalog views. Doing so allows access to a great wealth of information from code, information that you can use in writing helpful utilities to manage objects in your database. For example, execute the following query to retrieve the definition for the stored procedure created in Recipe 17-3:

SELECT definition
FROM sys.sql_modules m
        INNER JOIN sys.objects o
        ON m.object_id = o.object_id
WHERE o.type = 'P'
        AND o.name = 'LookupByAccount';

Your results will be the following output showing the definition in the form of a CREATE PROCEDURE statement. (If outputting as text, be sure to set the maximum number of characters displayed in each column to something higher than the default of just 256.)

definition
-------------------------------------------------------------------------------
CREATE PROCEDURE dbo.LookupByAccount
(@AccountNumber VARCHAR(10),
 @UpperFlag CHAR(1) = 'x')
AS
       SELECT     CASE UPPER(@UpperFlag)
                    WHEN 'U' THEN UPPER(FirstName)
                    ELSE FirstName
                  END AS FirstName,
                  CASE UPPER(@UpperFlag)
                    WHEN 'U' THEN UPPER(LastName)
                    ELSE LastName
                  END AS LastName
       FROM       Person.Person
       WHERE      BusinessEntityID IN (SELECT CustomerID
                                       FROM   Sales.Customer
                                       WHERE  AccountNumber = @AccountNumber) ;

You can save these results and execute them to re-create the procedure at some future time or on another database server.

How It Works

Invoke sp_helptext whenever you want to see the definition for a stored procedure or other user-defined object. You’ll get the result back in the form of a single text value.

Query the view sys.sql_modules to retrieve the definitions of stored procedures, triggers, views, and other SQL-defined objects. Join sys.sql_modules to sys.objects to gain access to object names and types. For example, the solution query specifically requested o.type = 'P'. That is the type code used to indicate stored procedures.

The two system views expose several other columns giving useful information or that you can use to restrict query results to only procedures and other objects of interest. It’s worth reviewing the view definitions (by visiting the SQL Server Books Online manual set) to become familiar with the values available.

Problem

You are writing a stored procedure and want to leave some notes for the next person (perhaps it will be yourself!) who must maintain that procedure.

Solution

Define a format for stored procedure headers that includes room for commentary and for a history of change over time. The following is an example of a standard stored procedure header:

CREATE PROCEDURE dbo.IMP_DWP_FactOrder AS
-- Purpose: Populates the data warehouse, Called by Job
-- Maintenance Log
-- Update By Update Date
Description
-- Joe Sack 8/15/2008 Created
-- Joe Sack 8/16/2008 A new column was added to
--the base table, so it was added here as well.
... Transact-SQL code here

For brevity, the stored procedure examples in this chapter have not included extensive comments or headers. However, in your production database, you should at the very least define headers for each stored procedure created in a production database.

How It Works

This recipe is more of a best practice rather than a review of a command or function. It is important to comment your stored procedure code very well so that future support staff, authors, and editors will understand the business rules and intents behind your Transact-SQL code. Although some code may seem self-evident at the time of authoring, the original logic may not seem so clear a few months after it was written. Business logic is often transient and difficult to understand over time, so including a written description of that logic in the body of the code can save hours of troubleshooting and investigation.

No doubt you’ll see other procedure headers out in the field with much more information. Don’t demand too much documentation. Include enough to bring clarity, but not so much that you introduce redundancy. For example, if you include the stored procedure name in the header, in addition to the actual CREATE PROCEDURE statement, you’ll soon start seeing code in which the header name doesn’t match the stored procedure name. Why not just document the information that isn’t already included in the stored procedure definition? That is the approach we recommend.

Problem

You are developing a stored procedure that invokes itself or a set of procedures that invoke each other. You want to detect programaticallyhow deeply nested you are in the call stack.

Solution

Execute a query to retrieve the @@NESTLEVEL value. This value begins at zero and is incremented by one for each procedure call. The following are two CREATE PROCEDURE statements to set up the solution example:

-- First procedure
CREATE PROCEDURE dbo.QuickAndDirty
AS
SELECT @@NESTLEVEL;
GO
-- Second procedure
CREATE PROCEDURE dbo.Call_QuickAndDirty
AS
SELECT @@NESTLEVEL
EXEC dbo.QuickAndDirty;
GO

After creating these two stored procedures, execute the following set of statements to demonstrate the operation of @@NESTLEVEL:

SELECT @@NESTLEVEL;
EXEC dbo.Call_QuickAndDirty;

Your results should be as follows:

-----------
        0
...
-----------
        1
...
-----------
        2

How It Works

@@NESTLEVEL returns the current nesting level for the stored procedure context. A stored procedure nesting level indicates how many times a stored procedure has called another stored procedure. SQL Server allows stored procedures to make up to a maximum of 32 nested (incomplete) calls.

The solution example begins by creating two stored procedures. One of those procedures invokes the other. The final query and procedure execution show that @@NESTLEVEL begins at zero. It is incremented and reported as 1 by the Call_QuickAndDirty procedure when that procedure is invoked by the EXEC statement. Then @@NESTLEVEL is incremented one more time when the first-invoked stored procedure executes QuickAndDirty.

Problem

You want to encrypt a stored procedure to prevent others from querying the system catalog views to view your code.

Solution

Create the procedure using the WITH ENCRYPTION option. Specify the option after the name of the new stored procedure, as the next example demonstrates:

CREATE PROCEDURE dbo.SEL_EmployeePayHistory
       WITH ENCRYPTION
AS
       SELECT     BusinessEntityID,
                  RateChangeDate,
                  Rate,
                  PayFrequency,
                  ModifiedDate
       FROM       HumanResources.EmployeePayHistory;

Once you’ve created WITH ENCRYPTION, you’ll be unable to view the procedure’s text definition. You can try to query for the definition:

EXEC sp_helptext SEL_EmployeePayHistory;

but you will receive only the following message:

The text for object 'SEL_EmployeePayHistory' is encrypted.

Even querying the system catalog directly won’t be of help. For example, you can try this:

SELECT definition
FROM   sys.sql_modules m
       INNER JOIN sys.objects o
           ON m.object_id = o.object_id
WHERE   o.type = 'P'
        AND o.name = 'SEL_EmployeePayHistory';

and you will be rewarded with only an empty result:

definition
----------
NULL

The procedure’s definition is encrypted, and there is nothing you can do to retrieve the definition. So, be sure to keep a copy outside the database.

How It Works

Stored procedure definitions can have their contents encrypted in the database, removing the ability to read a procedure’s definition later. Software vendors who use SQL Server in their back end often encrypt stored procedures in order to prevent tampering or reverse engineering from clients or competitors. If you use encryption, be sure to save the original T-SQL definition, because it can’t easily be decoded later (legally and reliably, anyhow). Also perform your encryption only prior to a push to production.

Problem

You want to specify the source for the rights and privileges under which a stored procedure executes. For example, you might want a caller to be able to execute a procedure but not also to have the privileges needed to execute the SELECT statements that the procedure executes internally.

Solution

Create or alter the procedure and specify the EXECUTE AS clause to define the security context under which a stored procedure is executed, regardless of the caller. The options for EXECUTE AS in a stored procedure are as follows:

EXECUTE AS { CALLER | SELF | OWNER | 'user_name' }

The default behavior for EXECUTE AS is the CALLER option, which means that the permissions of the executing user are used (and if the user doesn’t have proper access, that execution will fail). If the SELF option is used, the execution context of the stored procedure will be that of the user who created or last altered the stored procedure. When the OWNER option is designated, the owner of the stored procedure’s schema is used. The user_name option is an explicit reference to a database user under whose security context the stored procedure will be executed.

The following example creates a version of SEL_Department that is owned by HumanResources. The clause EXECUTE AS OWNER specifies that invocations of the procedure will run under the rights and privileges granted to the schema owner.

CREATE PROCEDURE HumanResources.SEL_Department
      @GroupName NVARCHAR(50)
WITH EXECUTE AS OWNER
AS
  SELECT     Name
  FROM       HumanResources.Department
  WHERE      GroupName = @GroupName
  ORDER BY   Name;
  SELECT     @@ROWCOUNT AS DepartmentCount;

How It Works

SQL Server implements a concept termed ownership chaining that comes into play when a stored procedure is created and used to perform an INSERT, UPDATE, DELETE, or SELECT against another database object. If the schema of the stored procedure object is the same as the schema of the object referenced within, SQL Server checks only that the stored procedure caller has EXECUTE permissions to the stored procedure.

Ownership chaining applies only to the INSERT, UPDATE, DELETE, or SELECT commands. This is why stored procedures are excellent for securing the database, because you can grant a user access to execute a stored procedure without giving the user access to the underlying tables.

An issue arises, however, when you are looking to execute commands that are not INSERT, UPDATE, DELETE, or SELECT. In those situations, even if a caller has EXECUTE permissions to a stored procedure that, for example, truncates a table using the TRUNCATE TABLE command, she must still have permissions to use the TRUNCATE TABLE command in the first place. You may not want to grant such broad permission.

Using EXECUTE AS, you can create the procedure to run as the schema owner or as a user that you specify. You need not grant permission for TRUNCATE TABLE to all users who might invoke the procedure but only to the user you specify in the security context.

The same “gotcha” goes for dynamic SQL within a stored procedure. SQL Server will ensure that the caller has both EXECUTE permission and the appropriate permissions in order to perform the task the dynamic SQL is attempting to perform, even if that dynamic SQL is performing an INSERT, UPDATE, DELETE, or SELECT. Specifying a security context lets you avoid granting those privileges broadly to all users who might need to invoke the procedure.

Problem

Your procedure produces wildly different query results based on the application calling it because of varying selectivity of qualified columns, so much so that the retained execution plan causes performance issues when varying input parameters are used.

Solution

Force a recompilation upon each invocation of the procedure. Do that by including the WITH RECOMPILE clause when creating (or altering) the procedure. Here’s an example:

ALTER PROCEDURE dbo.LookupByAccount2
  (
       @UpperFlag VARCHAR(1) = 'x',
       @AccountNumber VARCHAR(10)
  )
  WITH RECOMPILE
AS
       SELECT     CASE UPPER(@UpperFlag)
                    WHEN 'U' THEN UPPER(FirstName)
                    ELSE FirstName
                  END AS FirstName,
                  CASE UPPER(@UpperFlag)
                    WHEN 'U' THEN UPPER(LastName)
                    ELSE LastName
                  END AS LastName
       FROM       Person.Person
       WHERE      BusinessEntityID IN (SELECT CustomerID
                                       FROM   Sales.Customer
                                       WHERE  AccountNumber = @AccountNumber) ;

Now whenever this procedure is called, a new execution plan will be created by SQL Server.

How It Works

Recompilations occur automatically when underlying table or other object changes occur to objects that are referenced within a stored procedure. They can also occur with changes to indexes used by the plan or after a large number of updates to table keys referenced by the stored procedure. The goal of an automatic recompilation is to make sure the SQL Server execution plan is using the most current information and not using out-of-date assumptions about the schema and data.

SQL Server is able to perform statement-level recompiles within a stored procedure, instead of recompiling the entire stored procedure. Because recompiles cause extra overhead in generating new plans, statement-level recompiles help decrease this overhead by correcting only what needs to be corrected.

After every recompile, SQL Server caches the execution plan for use until the next time a change to an underlying object triggers another recompile. Cached query plans are a good thing but sometimes can cause inefficient plans to be chosen. Parameter sniffing, for example, is the process of deferring the generation of an execution plan until the first invocation of a query or procedure, at which time parameter values are examined and a plan is chosen based upon those values passed that very first time. The problem sometimes arises that a plan good for one set of values is actually terrible with another set. The problem can sometimes be bad enough that it is best to recompile at each execution. That is what the solution example accomplishes.

The solution example specifies WITH RECOMPILE to ensure that a query plan is not cached for the procedure during creation or execution. It is rare to need the option, because generally the cached plan chosen by SQL Server will suffice. Use the option if you want to take advantage of a stored procedure’s other benefits such as security and modularization but don’t want SQL Server to store an inefficient plan (such as from a “parameter sniff”) based on wildly varying result sets.

Problem

You want to remove all cached query plans from the plan cache. For example, you might want to test procedure performance against a so-called cold cache, reproducing the cache as though SQL Server had just been restarted.

Solution

Execute the DBCC FREEPROCCACHE command to clear existing cached plans. If you like, you can query the number of cached query plans first. Here’s an example:

SELECT COUNT(*) 'CachedPlansBefore'
FROM sys.dm_exec_cached_plans;

CachedPlansBefore
-----------------
        20

This example shows 20 cached plans. Your results may vary, depending upon the number of procedures you have executed. Clear the cached plans by executing DBCC FREEPROCCACHE as follows, and retrieve the number of cached plans again. Here’s an example:

DBCC FREEPROCCACHE;
SELECT COUNT(*) 'CachedPlansAfter'
FROM sys.dm_exec_cached_plans;

You should see output similar to the following:

DBCC execution completed. If DBCC printed error messages, contact your system administrator.
CachedPlansAfter
----------------
        0

How It Works

DBCC FREEPROCCACHE clears the procedure cache. The count of cached plans both before and after will vary based on the activity on your SQL Server instance. The query against sys.dm_exec_cached_plans shows one way to retrieve the count of plans currently in the cache. Background processes and jobs that may be running before and after the clearing of the cache can affect the results, and you may not necessarily see a zero for the number of cached plans after you’ve cleared the cache.