SQL 2005+

SQL Server Stored Procedures

The twenty-third part of the SQL Server Programming Fundamentals tutorial describes stored procedures. A stored procedure allows you to create a script, or batch, containing multiple commands to be executed, potentially being controlled by parameters.

What is a Stored Procedure?

A SQL Server stored procedure is a stored script or batch containing one or more Transact-SQL (T-SQL) statements that can be executed with a single command. The script can include any T-SQL statements, including those that query the database or create, modify or delete data in tables, as well as flow-of-control commands, mathematical operators and many predefined functions.

Stored procedures are often used to provide a level of abstraction for a database, hiding implementation details. Rather than permitting users from reading or updating information directly, you can create stored procedures that carefully control these operations and ensure that business rules are not broken. For example, in a banking system you may need to perform several updates to transfer money between accounts, ensuring that one account is credited, a second is debited and a third table is updated in a single transaction to record the transfer correctly and safely. By creating a stored procedure that includes all of these commands, a transfer can be executed in a single named call and in a consistent manner.

In the account transfer example, the transfer requires the knowledge of information such as a source and destination account and the amount of money to be moved. To allow such information to be used by a stored procedure, you can define parameters that accept values of any of the SQL Server data types. These are known as input parameters, as they are passed into the stored procedure and can be used freely within. The use of stored procedure parameters can be helpful when trying to avoid SQL injection attacks.

Stored procedures also permit the use of output parameters so that information can be returned to the caller of the batch. These allow information to be returned from the stored procedure in addition to the single return value that usually reports the status of the procedure. Of course, you can also query tables from within a stored procedure and return the results of the query as a recordset.

Some developers prefer not to use stored procedures in their applications. There are many reasons for this, one important one being the introduction of object-relational mapping (ORM) frameworks that use dynamically generate SQL statements to convert between data in a database and objects in an object-oriented programming language. There are many advantages to such an approach and a great deal of flexibility but also some disadvantages. Key to the disadvantages is that ORM frameworks generally require direct access to the database tables. This removes a level of abstraction and also limits security options. With stored procedures, you can restrict direct table access and force the used of the procedures, ensuring business rules are not overridden. Secondly, using a stored procedure only requires the sending of the name and parameters to the server. If a client performs complex functionality that requires many hundreds or thousands of statements to be executed, each of these may be sent over the network, generating unnecessary traffic. Some ORM tools recognise this and can call stored procedures, combining the use of the two technologies.

In this article we will create some stored procedures and examine the use of parameters. The examples will be reasonably simple and concentrate on the overall structure of a script rather than the fine detail. Over the rest of the tutorial we will be examining many more T-SQL commands including operators, functions, loops and conditional processing statements. Each of these can be used within stored procedures to create powerful scripts.

Creating Stored Procedures

In this article we will examine the creation of stored procedures against the JoBS tutorial database. This is the database that has been created throughout the course of the tutorial. If you do not have an up to date version of the database, you can use the link at the top of this article to download a script. Execute the script to generate the database and populate it with sample data.

The Stored Procedure Template

Stored procedures are created using the T-SQL "CREATE PROCEDURE" command. This command is used to specify the name of the new batch, any parameters that are required and the code of the procedure itself. Although the command can be typed manually into a query window, SQL Server Management Studio (SSMS) provides a template to get you started. To display the template, open SSMS and expand the JoBS database in the Object Explorer. You should see a node in the tree named "Programmability". Expand this branch of the tree to see the available elements for programming SQL Server. In this case, right-click the Stored Procedures section and select "New Stored Procedure..." from the menu that appears. A new query window containing the skeleton of a stored procedure is opened. The template will be similar to that shown below (though may vary depending upon the specific version of SQL Server that you are using):

-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName> 
  -- Add the parameters for the stored procedure here
  <@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_For_Param1, , 0>, 
  <@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_For_Param2, , 0>
AS
BEGIN
  -- SET NOCOUNT ON added to prevent extra result sets from
  -- interfering with SELECT statements.
  SET NOCOUNT ON;

  -- Insert statements for procedure here
  SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
END
GO

The template includes various items that are useful when creating a stored procedure. Some of the key elements are described in the following sections.

Comments

Comments can be added to stored procedure to describe the script. These are non-functional and for reference purposes only. Comments in stored procedures can be defined in two ways. Firstly, placing two consecutive hyphens (--) on a line signifies that the remainder of the line is a comment. Secondly, a block of text that spans lines can be identified as a comment by placing it between two special markers. The start-of-comment marker is a forward slash and an asterisk (/*). The end of the comment is marked with the same characters but this time reversed (*/).

-- This is a comment!

/* This block of text is
   also a comment! */

The stored procedure template includes several comments, including two large blocks. The first block will not be stored when the procedure is created. It provides some information for the creator of the new stored procedure.

The second block of comments is included when the stored procedure is saved to the database. It is optional, so can be removed if you would prefer not to keep it. The comments here are used to specify information about the new script, including the author, creation date and a brief description. You can type this additional information manually or use the "Specify Values for Template Parameters" dialog box to make the task easier. To display the box, press Ctrl-Shift-M.

The dialog box shows a grid containing a row for each comment item, the stored procedure name and information for the two parameter placeholders that are included in the template. In this case, enter your name in the Value column for the Author row. Add the current date and enter "Retrieves details of contract jobs." for a description. We can also provide a procedure name, in this case "GetContractJobs". Click the OK button to return to the query window. You should see that the information you entered has been included in the template.

CREATE PROCEDURE Command

The CREATE PROCEDURE command is responsible for the creation of the stored procedure. For our first script, we will not require any parameters so remove the template parameters from the statement. You can also remove the comments that appear within the body of the procedure (between the BEGIN and END commands). The command should now appear as follows:

CREATE PROCEDURE GetContractJobs AS
BEGIN
    SET NOCOUNT ON;

    SELECT @p1, @p2
END

SET NOCOUNT ON

When you execute a T-SQL statement, the number of rows returned or affected by the command is added to the results. In SSMS, this information is displayed beneath the query's results. However, this additional information is often unused and becomes additional network traffic. Worse, for some systems it can actually cause problems. For this reason, the default option in a new stored procedure created using the template is to disable this text using the "SET NOCOUNT ON" command.

Creating a Simple Stored Procedure

With the template almost complete, we can now add the body of the stored procedure. This contains the commands that will be executed whenever the procedure is called. For our first example we will create a very simple script that queries the indexed view that we created in the previous article.

Replace the template's SELECT statement with the following code, ensuring that you retain the SET NOCOUNT ON and END elements:

SELECT
    ContractNumber,
    JobName,
    StandardPrice,
    ContractValue
FROM
    ContractJobs

To create the stored procedure and save it in the database for future use, execute the script by pressing F5. A success message will be displayed and if you refresh the Stored Procedures section of the Object Explorer you will see the new stored procedure. Once you have created the new object, close the query window.

Executing a Stored Procedure

Now that we have a stored procedure, we can execute it simply by typing its name into a new query window and pressing F5. Open a new query window and run the following. You should see the results of the stored query displayed.

This syntax works because the stored procedure is the only command being executed. If you wish to call a stored procedure in a script that contains multiple statements, you must use the EXECUTE command, which may be abbreviated to EXEC, as in the following example:

EXECUTE GetContractJobs
EXEC GetContractJobs

Finally, we can use SSMS to generate the commands to execute a stored procedure. To do so, right-click the name of the stored procedure in the Object Explorer. From the context-sensitive menu, select "Execute Stored Procedure". A dialog box will be displayed. If the stored procedure had parameters, this dialog box would show them and permit you to specify the values that you wished to use for each. However, for this simple procedure there are no configuration options so click OK to generate the script.

DECLARE @return_value int

EXEC @return_value = [dbo].[GetContractJobs]

SELECT 'Return Value' = @return_value

The key statements in the new query are shown above. The DECLARE command creates a new variable named @return_value that can contain an integer value. All variables are named in this fashion with a prefix of "@". The variable will be assigned the return value from the stored procedure. In this case the return value will default to zero, as we have not specified any other value.

The second line executes the stored procedure using the EXEC keyword. You can see that the two-part naming convention is used when SSMS generates the commands for us. This line also assigns the return value of the stored procedure to the @return_value variable.

The final statement executes a new query. This query does not retrieve data from a table. Instead, it generates its own results by naming a column and selecting the value from the @return_value variable. In an SSMS query, this will add a second recordset to the results window. The second recordset will include a single row containing the return value. Press F5 to execute the query.

Returning a Value from a Stored Procedure

Stored procedures always return an integer value. As we have already seen, if the return value is not specified it will be zero. If a stored procedure can complete in a variety of possible states, it is useful to utilise the return value to specify the state according to a set of conditions. For example, the stored procedure may return zero for success or another value to indicate a warning or error. NB: It is conventional to return zero to indicate success. Other values may be a sign of success or failure or signify any other information that you wish.

Values are returned from a stored procedure using the RETURN command. This command halts execution of the stored procedure immediately, even if there are further commands in the batch. The RETURN statement used alone ends processing and returns zero. If you wish to return an alternative integer value, you supply it after the RETURN command.

To avoid overcomplicating this introductory article, we will not look at conditional processing until later in the tutorial. Instead, we will create a very simple stored procedure that does nothing except return the number fifty-two. Create the stored procedure by executing the following:

CREATE PROCEDURE ReturnValueDemo AS
BEGIN
    SET NOCOUNT ON
    RETURN 52
END

Use the "Execute Stored Procedure" option from the Object Explorer to generate the command to run the script and then press F5. You will see the return value in the results window.

Adding Parameters

Most stored procedures require information from the outside world to be passed to them to modify their behaviour. This information can be provided using parameters. Each parameter must be defined in the declaration area of the stored procedure and given a name and data type. The names follow the standard naming conventions for variables, as the parameters will be used as variables within the procedure.

In the next example, we will create a stored procedure that generates a new business customer in the JoBS database. This requires three pieces of information to be passed into the procedure so that the business name and the primary contact's first and last names can be specified. To add these parameters, they are provided as a comma-separated list between the name of the new script and the AS keyword. In each case, the name of the parameter is specified first, a space is included and the data type follows. The data types in this example will match those of the columns to be inserted into.

Once the parameters are defined, they can be used in place of literal values. In this case, we will replace the values in an insert statement with the parameter variables. To create the procedure that inserts new business customers, execute the following script:

CREATE PROCEDURE CreateBusinessCustomer 
    @BusinessName VARCHAR(100),
    @ContactFirstName VARCHAR(25),
    @ContactLastName VARCHAR(25)
AS
BEGIN
    SET NOCOUNT ON
    INSERT INTO Customers
        (BusinessName, FirstName, LastName)
    VALUES
        (@BusinessName, @ContactFirstName, @ContactLastName)
END

Executing a Parameterised Stored Procedure

There are several variations of syntax available when you wish to execute a stored procedure that includes parameters. The simplest requires that you provide the name of the procedure and a comma-separated list containing values for each parameter. The values must be in the same order as they are defined in the stored procedure. As with previous examples, if you wish to use the procedure as anything other than the first statement in a script, you must also precede the call with the EXEC command. Using the CreateBusinessCustomer, we can insert a new Customer row as follows:

One problem with this syntax is that the stored procedure definition may be changed, causing the parameters to be incorrectly assigned. In many cases this will cause an error when you try to run the procedure. However, a worse situation could be that the invalid parameters are accepted and allow the stored procedure to apparently run correctly whilst generating invalid results. To avoid this problem, you can specify values for names parameters explicitly. You can then provide the parameters in an order that is different to that of the procedure's signature. A modified version of the above sample could be:

CreateBusinessCustomer
    @ContactLastName='Green',
    @ContactFirstName='Fred',
    @BusinessName='ABC Blinds'

This syntax permits the stored procedure parameters to be reordered without causing a problem. You can even add new parameters to the stored procedure without breaking the call, if the new parameters have default values.

Adding Default Parameter Values

Sometimes it is useful to include optional parameters in a stored procedure. These parameters do not need to be supplied when calling the script. If they are not provided, the parameters will automatically be populated with default values. A default value is specified by adding an equals sign (=) to the parameter name and supplying either a compatible value or NULL for the default. In the following sample, the procedure can be used to create business or non-business customers. For non-business customers, the @BusinessName parameter can be excluded from the call and will be automatically set to NULL.

CREATE PROCEDURE CreateCustomer 
    @BusinessName VARCHAR(100) = NULL,
    @ContactFirstName VARCHAR(25),
    @ContactLastName VARCHAR(25)
AS
BEGIN
    SET NOCOUNT ON
    INSERT INTO Customers
        (BusinessName, FirstName, LastName)
    VALUES
        (@BusinessName, @ContactFirstName, @ContactLastName)
END

To demonstrate executing the procedure without the @BusinessName parameter, try the following:

CreateCustomer
    @ContactFirstName='Jim',
    @ContactLastName='Jasper'

Adding Output Parameters

The parameters described above have all been input parameters. This means that the values supplied to the parameters can only be used to send information into the stored procedure and not out of it. If the values of the parameters are modified within the batch of statements, the changes are not seen outside of the procedure.

Sometimes it is useful to return multiple values to the calling routine. This can be achieved with output parameters. Output parameters are dual-purpose. They still work as a means to pass information into the stored procedure if desired. In addition, any changes to the values within the stored procedure are visible outside of the batch too.

We can demonstrate this with a very simple stored procedure. In this case we will not query the database. We will instead perform some basic mathematical operations. The following stored procedure includes two input parameters that are used to pass two integer values into the batch. It also includes two output parameters that can be used to return the sum and the product of those two values. As you can see, the output parameters are identified as such by adding the OUTPUT keyword after the parameter. To set a return value, the parameters are simply assigned.

NB: This is an unusual example of the use of a stored procedure but is useful for example purposes.

CREATE PROCEDURE OutputDemo 
    @Value1 INT,
    @Value2 INT,
    @Sum INT OUTPUT,
    @Product INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON
    SET @Sum = @Value1 + @Value2
    SET @Product = @Value1 * @Value2
END
GO

When you execute a stored procedure that includes output parameters you have the option of returning or ignoring their output. If you use that standard syntax to call the script the output parameters will be used as if they were input parameters. To allow the procedure to return output parameter values you must use variables for the parameters and you must also add the OUTPUT keyword to each parameter that will return an updated value.

We can see this by running the following script. The first statement runs the stored procedure and returns the desired values. The second statement shows the values in the results window. Try removing the OUTPUT keywords and running the script again to see that, without the OUTPUT, the parameters do not return updated values.

DECLARE @ReturnedSum INT
DECLARE @ReturnedProduct INT

EXEC OutputDemo
    5,
    6,
    @ReturnedSum OUTPUT,
    @ReturnedProduct OUTPUT

SELECT
    'Sum' = @ReturnedSum,
    'Multiple' = @ReturnedProduct

Modifying a Stored Procedure

Stored procedures can be modified easily using the ALTER PROCEDURE command. This uses an almost identical syntax to the CREATE PROCEDURE statement. It permits you to modify the parameters and the body of the stored procedure. For example, we can add a new output parameter and a new calculation to the OutputDemo stored procedure as follows.

NB: The Abs command returns the absolute, positive value of a number; positive numbers are unchanged but negative values are made positive.

ALTER PROCEDURE OutputDemo
    @Value1 INT,
    @Value2 INT,
    @Sum INT OUTPUT,
    @Multiple INT OUTPUT,
    @Difference INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON
    SET @Sum = @Value1 + @Value2
    SET @Multiple = @Value1 * @Value2
    SET @Difference = abs(@Value1 - @Value2)
END

Once you have updated the stored procedure, you must also change the way that you call it to include the new parameter:

DECLARE @ReturnedSum INT
DECLARE @ReturnedProduct INT
DECLARE @ReturnedDifference INT

EXEC OutputDemo
    5,
    6,
    @ReturnedSum OUTPUT,
    @ReturnedProduct OUTPUT,
    @ReturnedDifference OUTPUT

SELECT
    'Sum' = @ReturnedSum,
    'Multiple' = @ReturnedProduct,
    'Difference' = @ReturnedDifference

Deleting a Stored Procedure

Stored procedures can be removed from a database using the DROP PROCEDURE command. The statement requires the name of the procedure to be deleted. To remove the OutputDemo script, execute the following: