Lesson 14. Stored Procedure Techniques Every Developer Should Know

The previous lesson covered the basics of working with stored procedures. To really be effective in working with stored procedures, you must learn additional techniques. In this lesson, you learn about:

The SET NOCOUNT statement

How to use the @@ functions in the stored procedures that you build

How to work with input and output parameters

How to add error handling to your stored procedures

Click here to view code image

CREATE PROCEDURE procPersonGetNoCount AS
SET NOCOUNT ON
SELECT BusinessEntityID, FirstName, LastName
FROM Person.Person
ORDER BY LastName, FirstName

If you execute this stored procedure from within SQL Server Management Studio, you’ll notice that the xx row(s) affected message does not appear (see Figure 14.1). You might wonder how with SET NOCOUNT ON in effect, you can return the number of rows affected to the client application. Fortunately, this is easily accomplished with the @@RowCount system variable. The following section covers the @@RowCount system variable as well as other system variables.

Click here to view code image

CREATE PROCEDURE procPersonGetNoCount AS
SET NOCOUNT ON
SELECT BusinessEntityID, FirstName, LastName
FROM Person.Person
ORDER BY LastName, FirstName
SELECT @@RowCount as NumberOfPeople

The example selects all employees who are sales representatives from the Employee table. It returns the number of rows selected (see Figure 14.2).

Click here to view code image

INSERT INTO Purchasing.ShipMethod
    (Name, ShipBase, ShipRate)
    VALUES ('Speedy Freight', 120, 150)
    SELECT @@Identity

The example inserts a row into the ShipMethod table. It returns the identity value of the inserted row (see Figure 14.3).

Click here to view code image

INSERT INTO Sales.SalesOrderDetail
    (CarrierTrackingNumber, OrderQty,
    ProductID, SpecialOfferID,
    UnitPrice, UnitPriceDiscount)
    VALUES (1, 1, 1, 1, 100.00, .05)
    SELECT @@Error

This example attempts to insert a row into the SalesOrderDetail table. If it is successful, @@Error returns zero. If you attempt to insert a sales order detail record without supplying a value for the SalesOrderID field, it returns error number 515, an error indicating that you cannot insert a NULL value into a column (see Figure 14.4).

It is important to note that @@Error returns the error number associated with the line of code immediately preceding it. Consider this example:

Click here to view code image

INSERT INTO Sales.SalesOrderDetail
    (CarrierTrackingNumber, OrderQty,
    ProductID, SpecialOfferID,
    UnitPrice, UnitPriceDiscount)
    VALUES (1, 1, 1, 1, 100.00, .05)
    SELECT @@Identity
    SELECT @@Error

Although the INSERT statement renders an error, the @@Error function returns zero (see Figure 14.5) because the SELECT @@Identity statement executes without error.

Click here to view code image

CREATE PROCEDURE procEmployeeGetYoungSalesReps
AS
SELECT BusinessEntityID, JobTitle,
    BirthDate, HireDate,
    VacationHours
FROM HumanResources.Employee
WHERE JobTitle Like 'Marketing %' AND
    BirthDate >= '1/1/1981'
ORDER BY BirthDate

Contrast this stored procedure with the following:

Click here to view code image

CREATE PROCEDURE procEmployeeGetByTitleAndBirthDate
    @JobTitle nVarChar(50),
    @BirthDate DateTime
AS
SELECT BusinessEntityID, JobTitle,
    BirthDate, HireDate,
    VacationHours
FROM HumanResources.Employee
WHERE JobTitle = @JobTitle AND
    BirthDate >= @BirthDate
ORDER BY BirthDate

The procedure receives two input parameters, @JobTitle and @BirthDate. The procedure uses these input parameters as variables for the WHERE clause for job title and birth date. The easiest way to execute this procedure from within SQL Server Management Studio is the following:

1. Right-click the procedure and select Execute Stored Procedure. The Execute Procedure dialog box appears. Notice in Figure 14.6 that the dialog box prompts you for the two parameters.

2. Enter the parameter values in the Value column and click OK. The results of executing the stored procedure appear.

Another method that you can use to execute the stored procedure is using the New Query toolbar option:

1. Click the New Query toolbar button. A blank query window appears.

2. Type the name of the procedure you want to execute, followed by the parameter values. You must separate each parameter with commas (see Figure 14.7).

3. Click Execute or press F5. The procedure executes.

The parameters in this stored procedure are required. If you do not supply them when you call the procedure, an error results (see Figure 14.8). The following is the same procedure with the job title set up as an optional parameter.

Click here to view code image

CREATE PROCEDURE procEmployeeGetByTitleAndBirthDateOpt
    @JobTitle nVarChar(50) = NULL,
    @BirthDate DateTime
AS
IF @JobTitle IS NULL
    BEGIN
    SELECT BusinessEntityID, JobTitle, BirthDate,
        HireDate, VacationHours
    FROM HumanResources.Employee
    WHERE BirthDate >= @BirthDate
    ORDER BY BirthDate
    END
ELSE
    BEGIN
    SELECT BusinessEntityID, JobTitle, BirthDate,
        HireDate, VacationHours
    FROM HumanResources.Employee
    WHERE JobTitle = @JobTitle AND
        BirthDate >= @BirthDate
    ORDER BY BirthDate
    END

You establish an optional parameter by supplying SQL Server with a default value for the parameter. In this example, @JobTitle is an optional parameter, but @BirthDate is required. If you opt to omit the @JobTitle parameter, you would call the stored procedure like this:

Click here to view code image

procEmployeesGetByTitleAndBirthDateOpt @BirthDate = '1/1/1960'

Notice that the example supplies the @BirthDate parameter as a named parameter. If you omit the @JobTitle parameter when you call the procedure, the procedure sets its value to Null. The stored procedure evaluates the value of the @JobTitle variable. If it is Null (it wasn’t supplied), the WHERE clause omits the title from the selection criteria. If the user of the stored procedures supplies the @JobTitle parameter, the procedure uses the @JobTitle parameter in the criteria for the WHERE clause.

Click here to view code image

CREATE PROCEDURE procEmployeesGetByTitleAndBirthDateOutput
    @JobTitle nVarChar(50) = NULL,
    @BirthDate DateTime,
    @MyMessage VarChar(50) = NULL OUTPUT
AS
IF @JobTitle IS NULL
    BEGIN
    SELECT BusinessEntityID, JobTitle, BirthDate,
        HireDate, VacationHours
    FROM HumanResources.Employee
    WHERE BirthDate >= @BirthDate
    ORDER BY BirthDate
    SELECT @MyMessage = 'No Title'
    END
ELSE
    BEGIN
    SELECT BusinessEntityID, JobTitle, BirthDate,
        HireDate, VacationHours
    FROM HumanResources.Employee
    WHERE JobTitle = @JobTitle AND
        BirthDate >= @BirthDate
    ORDER BY BirthDate
    SELECT @MyMessage = 'Job Title Supplied'
    END
SELECT @MyMessage

In addition to receiving two parameters, this procedure also has an output parameter called @MyMessage. The IF statement within the procedure sets the value of @MyMessage to the appropriate string (see Figure 14.9). You will see additional examples of OUPUT parameters and their uses as you move through the material. In particular, you will see examples of ADO code that utilize the output parameters within the client/server applications you build.

Click here to view code image

Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'SalesOrderID',
table 'AdventureWorks.Sales.SalesOrderDetail';
column does not allow nulls. INSERT fails.
The statement has been terminated.

Here’s an example of how you can prevent this error message from ever occurring:

Click here to view code image

CREATE PROCEDURE procOrderDetailAddHandleErrors
@SalesOrderID int = Null,
@CarrierTrackingNumber nvarchar(25) = Null,
@OrderQty smallint = 0,
@ProductID int = Null,
@SpecialOfferID int = Null,
@UnitPrice money = 0,
@UnitPriceDiscount money = 0
AS

IF @SalesOrderID Is Null
    BEGIN
        PRINT 'SalesOrderID Cannot be Null'
        RETURN
    END

IF @CarrierTrackingNumber Is Null
    BEGIN
        PRINT 'CarrierTrackingNumber Cannot be Null'
        RETURN
    END

IF @ProductID Is Null
    BEGIN
        PRINT 'ProductID Cannot be Null'
        RETURN
    END

IF @SpecialOfferID Is Null
    BEGIN
        PRINT 'SpecialOfferID Cannot be Null'
        RETURN
    END

INSERT INTO Sales.SalesOrderDetail
(SalesOrderID, CarrierTrackingNumber, OrderQty,
ProductID, UnitPrice, UnitPriceDiscount)
VALUES
(@SalesOrderID, @CarrierTrackingNumber, @OrderQty,
@ProductID, @UnitPrice, @UnitPriceDiscount)

In the example, several of the parameters are optional. The procedure begins by testing to see whether the value of the @SalesOrderID is Null. If it is, a message is printed, and the RETURN statement exits the procedure. This prevents the error message from ever occurring. The code repeats the process of testing the value and printing a message for both the CarrierTrackingNumber and ProductID parameters. Of course, you could add an output parameter that would report the problem back to the client application.

Click here to view code image

CREATE PROCEDURE procOrderDetailAddHandleErrors2
@SalesOrderID int,
@CarrierTrackingNumber nvarchar(25),
@OrderQty smallint,
@ProductID int,
@UnitPrice money,
@UnitPriceDiscount money
AS
DECLARE @SalesOrderDetailID int,
    @LocalError int,
    @LocalRows int

INSERT INTO Sales.SalesOrderDetail
(SalesOrderID, CarrierTrackingNumber, OrderQty,
ProductID, UnitPrice, UnitPriceDiscount)
VALUES
(@SalesOrderID, @CarrierTrackingNumber, @OrderQty,
@ProductID, @UnitPrice,
@UnitPriceDiscount)

SELECT @SalesOrderDetailID = @@Identity,
    @LocalError = @@Error, @LocalRows = @@RowCount
SELECT @SalesOrderDetailID, @LocalError, @LocalRows

The procedure first declares three variables: @SalesOrderID, @LocalError, and @LocalRows. It then inserts an order into the SalesOrderDetail table. The statement immediately following the INSERT statement populates the three variables with the identity value, error number (if any), and number of rows affected.

In Figure 14.10, you can see that the @SalesOrderID parameter was not passed to the stored procedure. Notice in the results that the identity value of the inserted row was NULL, the error number was 515, and the number of rows affected was 0. This is because an error occurred and the row was never inserted.

The alternative to this technique is to use output parameters. Here’s an example:

Click here to view code image

CREATE PROCEDURE procOrderDetailAddHandleErrors3
@SalesOrderID int,
@CarrierTrackingNumber nvarchar(25),
@OrderQty smallint,
@ProductID int,
@UnitPrice money,
@UnitPriceDiscount money,
@SalesOrderDetailID int = 0 OUTPUT,
@LocalError int = 0 OUTPUT,
@LocalRows int = 0 OUTPUT
AS
INSERT INTO Sales.SalesOrderDetail
(SalesOrderID, CarrierTrackingNumber, OrderQty,
ProductID, UnitPrice, UnitPriceDiscount)
VALUES
(@SalesOrderID, @CarrierTrackingNumber, @OrderQty,
@ProductID, @UnitPrice, @UnitPriceDiscount)
SELECT @SalesOrderDetailID = @@Identity,
    @LocalError = @@Error, @LocalRows = @@RowCount
SELECT @SalesOrderDetailID, @LocalError, @LocalRows

Notice that the procedure does not declare any variables. Instead, it contains three output parameters: one for the SalesOrderDetailID, another for the error information, and the last for the number of rows affected. The procedure populates the output parameters just as it populated the variables, with a SELECT statement immediately following the INSERT statement.

A. When it is set to ON, SET NOCOUNT eliminates the xx row(s) affected message in the SQL Express Manager window and the DONE_IN_PROC communicated from SQL Server to the client application. This improves the performance of the stored procedure.

Q. The @@ functions are actually variables (true/false).

A. False. You cannot assign values to them or work with them like normal variables. They are instead functions that return information about SQL Server.

Q. Describe the parameters used when a stored procedure is used to insert data.

A. You generally have one input parameter for each field in the underlying table. It is also common to have output parameters that return error and status information, as well as the identity value of the row that the stored procedure inserted.

2. What keyword do you use to return a value from a stored procedure?

3. You use the ON ERROR GOTO statement to write error handling into the stored procedures that you build (true/false).

4. What is the TSQL function you use to determine whether the caller has passed a parameter value to a stored procedure?

2. The OUTPUT keyword.

3. False. The ON ERROR GOTO statement is not available in T-SQL stored procedures. You must therefore handle errors as they occur.

4. The ISNULL keyword.