Lesson 3:Programming Stored Procedures | MCSE Training Kit (Exam 70-229): Microsoft SQL Server(TM) 2000 Database Design and Implementation (Pro Certification)

3 4

Stored procedures provide a powerful method for coding tasks in the Transact-SQL language. In the previous lesson, you learned how to create, alter, drop, and execute stored procedures. You also saw how to use an input parameter to pass a value to a stored procedure when it executes. This lesson explores the use of both input and output parameters and introduces variables. You will also learn how to handle errors in your stored procedures with return codes and the @@ERROR function. The last part of this lesson introduces you to nesting and using cursors in stored procedures.

After this lesson, you will be able to:

Use parameters, return codes, variables, and cursors in stored procedures.
Create nested stored procedures and error-handling code.

Estimated lesson time: 45 minutes

Parameters and Variables

Parameters and variables are a fundamental part of making a stored procedure dynamic. Input parameters enable the user who is running the procedure to pass values to the stored procedure, making the result set unique to the request. Output parameters extend the output of your stored procedures beyond the standard result sets returned from a query. The data from an output parameter is captured in memory when the procedure executes. To return a value from the output parameter, you must create a variable to hold the value. You can display the value with SELECT or PRINT commands, or you can use the value to complete other commands in the procedure.

You might be a bit more familiar with input parameters than with output parameters. You used an input parameter (@CustomerID) when you created the CustOrderHistRep stored procedure in Lesson 2. Before running CustOrderHistRep, you set the @CustomerID parameter equal to thecr, and then you executed the procedure.

In summary, an input parameter is defined in a stored procedure, and a value is provided to the input parameter when the procedure is executed. An output parameter is defined in a stored procedure by using the OUTPUT keyword. When the procedure executes, a value for the output parameter is stored in memory. To put the value somewhere useful, you declare a variable to hold the value. Output values are typically displayed when the procedure execution completes.

The following procedure shows the use of both input and output parameters:

 USE Pubs GO CREATE PROCEDURE dbo.SalesForTitle   @Title varchar(80),   -- This is the input parameter.   @YtdSales int OUTPUT, -- This is the first output parameter.   @TitleText varchar(80) OUTPUT --This is the second output parameter. AS   -- Assign the column data to the output parameters and -- check for a title that's like the title input parameter.  SELECT @YtdSales = ytd_sales, @TitleText=title FROM titles WHERE title LIKE @Title GO

The input parameter is @Title, and the output parameters are @YtdSales and @TitleText. Notice that all three parameters have defined data types. The output parameters include the mandatory OUTPUT keyword. After the parameters are defined, the SELECT statement uses all three parameters. First, the output parameters are set equal to the column names in the query. When the query is run, the output parameters will contain the values from these two columns. The WHERE clause of the SELECT statement contains the input parameter, @Title. When the procedure is executed, you must provide a value for this input parameter or the query will fail. Later in this lesson, you will learn how to handle errors and provide default values for parameters.

The following statement executes the stored procedure that you just examined:

 -- Declare variables to receive output values from procedure. DECLARE @y_YtdSales int, @t_TitleText varchar(80) EXECUTE SalesForTitle --set the values for output parameters to the variables. @YtdSales = @y_YtdSales OUTPUT,  @TitleText = @t_TitleText OUTPUT, @Title = "%Garlic%" --specify a value for input parameter. -- Display the variables returned by executing the procedure. Select "Title" = @t_TitleText, "Number of Sales" = @y_YtdSales GO

Two variables are declared: @y_YtdSales and @t_TitleText. These two variables will receive the values stored in the output parameters. Notice that the data types declared for these two variables match the data types of their corresponding output parameters. These two variables can be named the same as the output parameters because the variables in a stored procedure are local to the batch that contains them. For clarity, the variable names are different than the output parameters. When the variable is declared, it is not matched with an output parameter. Variables are matched with output parameters after the EXECUTE statement. Notice that the OUTPUT keyword is specified when the output parameters are set as equal to the variables. If OUTPUT is not specified, the variables cannot display values in the SELECT statement at the bottom of this batch. Finally, notice that the input parameter @Title is set equal to %Garlic%. This value is sent to the WHERE clause of the stored procedure's SELECT statement. Because the WHERE clause uses the LIKE keyword, you can use wildcards such as % so that the query searches for titles that contain the word "Garlic."

A more succinct way to execute the procedure is shown next. Notice that it is not necessary to specifically assign the variables from the stored procedure to the value of the input parameter or the output variables declared here:

 DECLARE @y_YtdSales int, @t_TitleText varchar(80) EXECUTE SalesForTitle "%Garlic%", --sets the value of the input parameter. @y_YtdSales OUTPUT, --receives the first output parameter @t_TitleText OUTPUT --receives the second output parameter -- Display the variables returned by executing the procedure. Select "Title" = @t_TitleText, "Number of Sales" = @y_YtdSales GO

When the procedure is executed, it returns the following:


Title	Number of Sales
Onions, Leeks, and Garlic: Cooking Secrets of the Mediterranean	375

An interesting result of this procedure is that only a single row of data is returned. Even if the SELECT statement in the procedure returns multiple rows, each variable holds a single value (the last row of data returned). Several solutions to this problem are explained later in this lesson.

The RETURN Statement and Error Handling

Often, the majority of coding in a well-written stored procedure (or in any program, for that matter) involves error handling. SQL Server provides functions and statements to deal with errors that occur during procedure execution. The two primary categories of errors are computer errors, such as an unavailable database server, and user errors. Return codes and the @@ERROR function are used to handle errors that occur when a procedure is executed.

Return codes can be used for other purposes besides error handling. The RETURN statement is used to generate return codes and exit a batch, and it can provide any integer value to a calling program. You will see code samples in this section that use the RETURN statement to supply a value for both error-handling and other purposes. The RETURN statement is used primarily for error handling because when the RETURN statement runs, the stored procedure exits unconditionally.

Consider the SalesForTitle procedure that you created and executed in the last section. If the value specified for the input parameter (@Title) does not exist in the database, executing the procedure returns the following result set:


Title	Number of Sales
NULL	NULL

It is more instructive to explain to the user that there were no matching records. The following example shows how to modify the SalesForTitle stored procedure to use a RETURN code (and thus provide a more useful message):

 ALTER PROCEDURE dbo.SalesForTitle @Title varchar(80), @YtdSales int OUTPUT,  @TitleText varchar(80) OUTPUT AS   --Check to see if the title is in the database. If it isn't,  --exit the procedure and set the return code to 1. IF (SELECT COUNT(*) FROM titles WHERE title LIKE @Title) = 0   RETURN(1) ELSE SELECT @YtdSales = ytd_sales, @TitleText=title FROM titles WHERE title LIKE @Title GO

The IF statement below the AS keyword determines whether the input parameter is provided when the procedure is executed and matches any records in the database. If the COUNT function returns 0, then the return code is set to 1, RETURN(1). If the COUNT function returns anything else, the SELECT statement queries the Title table for annual sales and book title information. In this case, the return code is equal to 0.

NOTE

The initial USE Pubs statement has been removed from the script because it isn't the focus of the sample code. If you run this code make sure that you have switched to the Pubs database first. As you move through the code examples, you might notice other space-saving code consolidations (such as removing code comments and adding others). You should make a point of commenting your code so that it is maintainable and relatively easy to understand.

Some recoding of the execution statement is necessary to use the return code. The following example sets the input parameter @Title equal to Garlic%:

 --Add @r_Code to hold the result code. DECLARE @y_YtdSales int, @t_TitleText varchar(80), @r_Code int --Run the procedure and set @r_Code equal to the procedure. EXECUTE @r_Code = SalesForTitle @YtdSales = @y_YtdSales OUTPUT,  @TitleText = @t_TitleText OUTPUT, @Title = "Garlic%"  --Determine the value of @r_Code and execute the code. IF @r_Code = 0  SELECT "Title" = @t_TitleText,  "Number of Sales" = @y_YtdSales, "Return Code" = @r_Code ELSE IF @r_Code = 1 PRINT 'No matching titles in the database. Return code=' + CONVERT(varchar(1),@r_Code) GO

A new variable has been added to the end of the DECLARE statement: @r_Code. Later in the code, this variable contains the value provided by the RETURN keyword. This variable is defined as an integer data type because the return code sends an integer value. The @r_Code variable is set to the return code on the EXECUTE line. Notice that @r_Code is set equal to the stored procedure. The value for the input parameter, @Title, is now Garlic% rather than %Garlic%. In other words, the SELECT statement in the procedure will search for records in the Titles table starting with the word "Garlic." Conditional logic appears below the parameters and comments. The first IF statement is tested. If the procedure finds a record, then the return code is 0, and the SELECT statement runs. If the procedure doesn't find any matching records, @r_Code will equal 1 and the PRINT statement will run. Because there isn't a title in the database that begins with the word "Garlic," executing the procedure returns the PRINT statement:

 No matching titles in the database. Return code=1

Changing the input parameter to %Garlic% and executing the code returns the following result set:


Title	Number Return of Sales	Code
Onions, Leeks, and Garlic: Cooking Secrets of the Mediterranean	375	0

If you don't enter a value for the @Title input parameter, the result of executing the query is the following message:

 Server: Msg 201, Level 16, State 3, Procedure SalesForTitle, Line 0 Procedure 'SalesForTitle' expects parameter '@Title', which was not supplied.

Default Values and Setting a Parameter to NULL

To test for a NULL value for the @Title input parameters, you must set a default value for the input parameter and modify the stored procedure by adding another conditional statement. Setting a default value is a powerful error-avoidance technique. If you set @Title to a value contained in the database, the SELECT statement would use the default for the input parameter. In this case, it's more instructive to the user if you set a default of NULL for the input parameter and let a RETURN code instruct the user on how to properly execute the procedure. Modifying the procedure in the following way accomplishes this goal:

 ALTER PROCEDURE dbo.SalesForTitle @Title varchar(80) = NULL, @YtdSales int OUTPUT,  @TitleText varchar(80) OUTPUT AS   IF @Title IS NULL   RETURN(2) ELSE IF (SELECT COUNT(*) FROM titles WHERE title LIKE @Title) = 0   RETURN(1) ELSE SELECT @YtdSales = ytd_sales, @TitleText=title FROM titles WHERE title LIKE @Title GO

Notice that @Title is set to a default value of NULL. A conditional IF statement was added right after the AS keyword. This test must occur before the SELECT COUNT statement, however, because the SELECT COUNT statement will test true with a value of NULL.

When you execute the procedure, you must include a test for a return code of 2, as shown in the following example:

 DECLARE @y_YtdSales int, @t_TitleText varchar(80), @r_Code int EXECUTE @r_Code = SalesForTitle @YtdSales = @y_YtdSales OUTPUT, @TitleText = @t_TitleText OUTPUT --No value specified for the @Title input parameter. IF @r_Code = 0  SELECT "Title" = @t_TitleText,  "Number of Sales" = @y_YtdSales, "Return code" = @r_Code ELSE IF @r_Code = 1 PRINT 'No matching titles in the database. Return code=' + CONVERT(varchar(1),@r_Code) ELSE IF @r_Code = 2 --test for a return code of 2. PRINT 'You must add a value for @Title for this procedure to function properly.  Return code=' + CONVERT(varchar(1),@r_Code) GO

Testing for Server Errors

Another important category of error to test for is database errors. The @@ERROR function enables you to test for more than 3000 different database-related errors. This function captures database error numbers as a procedure executes. You can use each return number to display a message to the user explaining the reason for the error. Common user errors that lead to database errors occur in INSERT and UPDATE statements in which a user attempts to add data that violates the integrity of the data (such as adding an invalid identification number). The error numbers and their descriptions are stored in the master.dbo.sysmessages table. You can query this table with the following SELECT statement:

 SELECT error, description from master.dbo.sysmessages

If a procedure executes successfully, @@ERROR is set to 0. If an error occurs, an error number other than 0 is returned to @@ERROR. When another Transact-SQL statement runs in a procedure, the value of @@ERROR changes. Therefore, @@ERROR should be stored in a variable or provide a return code after each Transact-SQL statement completes. The following example demonstrates how to generate a return code if there is a database error:

 ALTER PROCEDURE dbo.SalesForTitle @Title varchar(80) = NULL, @YtdSales int OUTPUT,  @TitleText varchar(80) OUTPUT AS IF @Title IS NULL   RETURN(2) ELSE IF (SELECT COUNT(*) FROM dbo.titles WHERE title LIKE @Title) = 0   RETURN(1) ELSE SELECT @YtdSales = ytd_sales, @TitleText=title FROM dbo.titles WHERE title LIKE @Title -- Check for SQL Server database errors. IF @@ERROR <> 0   RETURN(3) GO

Notice that the procedure checks for the value of @@ERROR after the SELECT statement that returns a result set when the procedure is executed. This function is important, because the goal of @@ERROR is to check for database errors after the core task of the stored procedure occurs.

The following code executes the procedure and displays a generic error message if a database error occurs:

 DECLARE @y_YtdSales int, @t_TitleText varchar(80), @r_Code int EXECUTE @r_Code = SalesForTitle @YtdSales = @y_YtdSales OUTPUT, @TitleText = @t_TitleText OUTPUT, @Title = "%Garlic%"  IF @r_Code = 0  SELECT "Title" = @t_TitleText,  "Number of Sales" = @y_YtdSales, "Return code" = @r_Code ELSE  IF @r_Code = 1 PRINT 'No matching titles in the database. Return code=' + CONVERT(varchar(1),@r_Code) ELSE IF @r_Code = 2 PRINT 'You must add a value for @Title for this procedure to function properly.  Return code=' + CONVERT(varchar(1),@r_Code) ELSE IF @r_Code = 3   PRINT 'There was a database error.' GO

When you create a stored procedure for a production database system, you will become familiar with the types of errors that users will make when they execute the procedure. With this experience and your understanding of result codes, you can write additional error-handling conditions into your procedures.

NOTE

For more information about result codes and using the @@ERROR function, perform a search for RESULT and @@ERROR in SQL Server Books Online.

Nesting Procedures

Nesting stored procedures simply involves calling one stored procedure from another. A single stored procedure can perform multiple tasks, but it is better to create simpler, more generic stored procedures that can call other stored procedures to complete additional tasks. For example, you might write a stored procedure called Procedure_A that inserts data into an existing table, and you might write another stored procedure called Procedure_B that displays data from the table. Procedure_A could call Procedure_B after successfully inserting data into a table. Users can still run Procedure_B to view table information without having to run Procedure_A to insert data into the table.

Procedures can be nested together up to 32 levels deep. However, there is no limit to how many procedures a single procedure can call. A procedure can also call itself recursively. A procedure is called from another procedure by using the EXECUTE statement. You will nest a procedure for the BookShopDB database in Exercise 3.

Cursors

You can use cursors in stored procedures; however, you should avoid them if you can find a way to do the same thing by using result sets. Procedures that use result sets are more efficient on the database and on the network, and they are typically less complex to write than cursors. Cursors are explored in Chapter 7, but are discussed here so that you can see how to use them in stored procedures. To learn more about cursors, read Lesson 3 in Chapter 7 of SQL Server Books Online and Inside Microsoft SQL Server 2000 by Microsoft Press.

Data Retrieval Methods

The stored procedure code example used throughout this lesson is limited to returning a single row of data. For example, an input parameter value of "The%" will return a single row of data, with the last title starting with "The%" (as shown in the following result):


Title	Number of Sales	Return Code
The Psychology of Computer Cooking	NULL	0

A single row appears because the result set returned by the SELECT statement is passed to a variable capable of holding only a single value.

There are a number of solutions to this problem. The simplest solution is to eliminate the output parameters in the SELECT statement and return a record set from the stored procedure, as shown:

 ALTER PROCEDURE dbo.SalesForTitle @Title varchar(80) AS   SELECT Title = title, [Number of Sales]=ytd_sales FROM titles WHERE title LIKE @Title GO

Notice that all output parameters were removed from the stored procedure. To execute this simple stored procedure, you can use the following sample code:

 EXECUTE SalesForTitle @Title = "The%"

When you execute this procedure, the result set is as follows:


Title	Number of Sales
The Busy Executive's Database Guide	4095
The Gourmet Microwave	22,246
The Psychology of Computer Cooking	NULL

For clarity, the return code syntax has been removed from the stored procedure; thus, the Return Code column does not appear. All book titles starting with "The%" appear in the result set.

The same data can be returned with cursors. The following stored procedure places each row of data into a cursor output parameter and returns the row of data before fetching the next row:

 ALTER PROCEDURE dbo.SalesForTitle @ResultCrsr CURSOR VARYING OUTPUT, @Title varchar(80) = NULL AS SET @ResultCrsr = CURSOR FOR SELECT ytd_sales, title FROM dbo.titles WHERE title LIKE @Title OPEN @ResultCrsr GO

The second line of code creates an output parameter called @ResultCrsr of the data type cursor. This output parameter will hold each record returned by the cursor. After the AS keyword, @ResultCrsr is set equal to the cursor that will eventually contain each record of the SELECT statement. After the SELECT statement, the OPEN statement populates the result set into the cursor.

The following code can be used to execute this stored procedure:

 DECLARE @r_ResultCrsr CURSOR EXECUTE dbo.SalesForTitle @ResultCrsr = @r_ResultCrsr OUTPUT, @Title = "The%" FETCH NEXT FROM @r_ResultCrsr WHILE (@@FETCH_STATUS <> -1)   BEGIN     FETCH NEXT FROM @r_ResultCrsr   END CLOSE @r_ResultCrsr DEALLOCATE @r_ResultCrsr GO

All three records from the database are returned. The output data is the same as the result set example shown earlier in this section, except that each record appears separately in the results pane. An additional empty row is returned because the @@FETCH_STATUS is not set to –1 until after an empty row is retrieved.

Exercise 3: Programming Stored Procedures to Insert and Retrieve Data

In this exercise, you will create a stored procedure to insert new customer records into the BookShopDB database. You will then create a stored procedure that checks for duplicate customer records in the database. The second procedure you create will be nested in the first procedure. The stored procedures will include parameters, variables, return codes, the @@ERROR function, and control of flow language.

To create an insert customer stored procedure

Open Query Analyzer and connect to your local server.
In the Editor pane of the Query window, enter and execute the following code:

 USE BookShopDB GO CREATE PROCEDURE dbo.AddCustomer --CustomerID not included as an input parameter because --the ID is automatically generated (Identity column) @FirstName varchar(30), @LastName varchar(30),  @Phone varchar(24), @Address1 varchar(60),  @Address2 varchar(60) = 'unknown', @City varchar(15), @State varchar(7), @Zip varchar(12) AS INSERT [BookShopDB].[dbo].[Customers]  (FirstName, LastName, Phone, Address1,  Address2, City, State, Zip) VALUES  (@FirstName, @LastName, @Phone, @Address1,  @Address2, @City, @State, @Zip) RETURN(SELECT @@IDENTITY AS 'Identity') GO

In this statement, you create a stored procedure named AddCustomer. You specify all of the required input parameters. Notice that a default value of `unknown' is provided for Address2. Address2 isn't always part of a customer's address. The value of `unknown' matches the default value of the check constraint applied to this column. At the end of the code, the RETURN statement contains the customer ID value, which is retrieved by the @@IDENTITY function.

In the Editor pane of the Query window, enter and execute the following code:

 DECLARE @r_Code int EXECUTE @r_Code=dbo.AddCustomer @FirstName = 'Jamie', @LastName = 'Marra', @Phone = '425-555-1212', @Address1 = '20 Oak St., SE', @City = 'Remy', @State = 'WA', @Zip = '98888' SELECT [Customer ID] = 'The new customer ID is:' + CONVERT(CHAR(2), @r_Code)

The code creates a variable of type integer named @r_Code. This variable is set equal to the stored procedure name upon execution. Lesson 3's code examples showed an example of using a return code for error handling. In this procedure, the return code will contain the value of the @@IDENTITY function when the stored procedure executes.

To add error-handling techniques to the stored procedure

The AddCustomer stored procedure does not contain any error handling. The procedure needs to be modified to include error-handling logic. In the Editor pane of the Query window, enter and execute the following code:

 ALTER PROCEDURE dbo.AddCustomer @FirstName varchar(30)= 'unknown', @LastName varchar(30)= 'unknown', @Phone varchar(24) = NULL, @Address1 varchar(60) = NULL,  @Address2 varchar(60) = 'unknown', @City varchar(15) = NULL,  @State varchar(7) = NULL, @Zip varchar(12) = NULL AS IF (@FirstName = 'unknown') AND (@LastName = 'unknown') RETURN(1) ELSE IF @Phone IS NULL RETURN(2) ELSE IF   @Address1 IS NULL OR @City IS NULL OR    @State IS NULL OR @Zip IS NULL RETURN(3) ELSE INSERT [BookShopDB].[dbo].[Customers]  (FirstName, LastName, Phone, Address1,  Address2, City, State, Zip) VALUES  (@FirstName, @LastName, @Phone, @Address1,  @Address2, @City, @State, @Zip) RETURN(SELECT @@IDENTITY AS 'Identity') IF @@ERROR <> 0 RETURN(4) GO

All input variables now contain default values. This method is an important error-avoidance technique. For example, if no value is specified for @FirstName, it will default to `unknown' and the procedure will execute properly. If the user doesn't enter a first name or a last name for the customer, however, the procedure returns a value of 1. This part of the code complies with the business rule that a first name or a last name is required, and it matches with the check constraint applied to the FirstName and LastName columns of BookShopDB. If no phone number is entered, the default value of NULL is used, and a return code of 2 is sent to the executing procedure. If any address information is missing, a return code of 3 is sent to the executing procedure. Notice that the @Address2 input parameter is not part of address error checking because it is not always required. If all input parameters are specified, the INSERT statement is called. If the INSERT statement fails because of a database error, a return code of 4 is sent to the executing program.

As you work with the stored procedure, you will want to add additional error handling to the code. For example, you should check the database for duplicate customer information before creating a new customer record.

Comments were left out of the code to save space. Make sure that any code you write is fully commented inline so that others will understand the purpose of your code (and to help you remember the purpose of your code). Add comments to the beginning of your code to summarize the purpose of the procedure, the creation date, and your name or initials.

In the Editor pane of the Query window, enter and execute the following code:

 DECLARE @r_Code int EXECUTE @r_Code=dbo.AddCustomer @FirstName= 'Jamie', @LastName = 'Marra', @Phone = '425-555-1212', @Address1 = '20 Oak St., SE', @City = 'Remy', @State = 'WA', @Zip = '98888' IF @r_Code = 4 BEGIN      PRINT 'A database error has occured.      Please contact the help desk for assistance.' END IF @r_Code = 1 PRINT 'You must specify a value for the firstname or lastname' ELSE IF @r_Code = 2 PRINT 'You must specify a value for the phone number' ELSE IF @r_Code = 3 PRINT 'You must provide all address information, Street address, City, State and Zipcode' ELSE IF @r_Code = @@IDENTITY SELECT [Customer ID] = 'The new customer ID is: ' + CONVERT(CHAR(2), @r_Code)

A new customer record is added to the database. Error-checking code appears throughout the execution code. Examine each @r_Code value and the information returned to the user in the event of an error.

Notice that the same customer information was entered as in the previous procedure execution. In the next exercise, you will create a stored procedure to check the Customers table to avoid creating duplicate customer information.

To create a stored procedure to protect against customer record duplication

Checking for duplicate record information is an important part of record entry. Without this check, it is inevitable that tables will contain duplicate entries. In the Editor pane of the Query window, enter and execute the following code:

 CREATE PROCEDURE dbo.CheckForDuplicateCustomer @1_FirstName varchar(30)= 'unknown', @1_LastName varchar(30)= 'unknown', @1_City varchar(15) = NULL, @1_State varchar(7) = NULL, @1_Phone varchar(24) = NULL, @o_FirstName varchar(30) OUTPUT, @o_LastName varchar(30) OUTPUT, @o_City varchar(15) OUTPUT,  @o_State varchar(7) OUTPUT, @o_Phone varchar(24) OUTPUT AS SELECT @o_FirstName=firstname, @o_LastName=lastname,  @o_City=city, @o_State=state, @o_Phone=phone FROM customers   WHERE firstname=@1_FirstName AND lastname=@1_LastName   AND city=@1_City AND state=@1_State AND phone=@1_Phone IF @@ROWCOUNT <> 0   RETURN(5)

This stored procedure checks the Customers table to determine whether there is a record with the same first name, last name, city, or phone number as the input parameters specified. The input parameters begin with 1 to distinguish them from the input parameters in the AddCustomer stored procedure. The output parameters are set equal to the corresponding Customers table columns in the SELECT statement. A test for a record match is made in the WHERE clause. The column values are tested against the input parameters. If the @@ROWCOUNT function returns a value greater than 0, then a return code of 5 is set for the stored procedure.

In the Editor pane of the Query window, enter and execute the following code:

 ALTER PROCEDURE dbo.AddCustomer @FirstName varchar(30)= 'unknown', @LastName varchar(30)= 'unknown', @Phone varchar(24) = NULL, @Address1 varchar(60) = NULL,  @Address2 varchar(60) = 'unknown', @City varchar(15) = NULL,  @State varchar(7) = NULL, @Zip varchar(12) = NULL AS IF (@FirstName = 'unknown') AND (@LastName = 'unknown')   RETURN(1) ELSE IF @Phone IS NULL   RETURN(2) ELSE IF @Address1 IS NULL OR @City IS NULL OR @State IS NULL OR @Zip IS NULL   RETURN(3) --begin nesting DECLARE @r_Code int, @v_FirstName varchar(30),  @v_LastName varchar(30), @v_City varchar(15),  @v_State varchar(7), @v_Phone varchar(24) EXECUTE @r_Code=dbo.CheckForDuplicateCustomer @1_FirstName = @FirstName, @1_LastName = @LastName, @1_City = @City, @1_State = @State, @1_Phone = @Phone, @o_FirstName = @v_FirstName OUTPUT,  @o_LastName = @v_LastName OUTPUT, @o_City = @v_City OUTPUT,  @o_State = @v_State OUTPUT, @o_Phone = @v_Phone OUTPUT IF @@ROWCOUNT > 0 BEGIN   PRINT 'A duplicate record was found for ' + @v_FirstName + ' ' + @v_LastName   PRINT 'in ' + @v_City + ' ' + @v_State + ' with a phone number '  PRINT 'of ' + @v_Phone + '.'   RETURN(5) END --end nesting INSERT [BookShopDB].[dbo].[Customers]  (FirstName, LastName, Phone, Address1, Address2, City, State, Zip) VALUES  (@FirstName, @LastName, @Phone, @Address1, @Address2, @City, @State, @Zip) RETURN(SELECT @@IDENTITY AS 'Identity') IF @@ERROR <> 0   RETURN(4) GO

The CheckForDuplicateCustomer stored procedure is nested into the AddCustomer stored procedure. The point at which nesting begins and ends has been commented in the code. Variables beginning with v_ are created to hold the output parameter values. Each input parameter beginning with 1_ is set equal to the corresponding input parameter supplied when the AddCustomer stored procedure executes. The output parameters from the CheckForDuplicateCustomer stored procedure are then set equal to their corresponding output variables beginning with v_. The variable values are used in the sequence of PRINT statements that are called if the @@ROWCOUNT returns anything greater than 0.

To test the stored procedures

In the Editor pane of the Query window, enter and execute the following code:

 DECLARE @r_Code int EXECUTE @r_Code=dbo.AddCustomer @FirstName= 'Jamie', @LastName = 'Marra', @Phone = '425-555-1212', @Address1 = '20 Oak St., SE', @City = 'Remy', @State = 'WA', @Zip = '98888' IF @r_Code = 4 BEGIN      PRINT 'A database error has occured.      Please contact the help desk for assistance.' END IF @r_Code = 1 PRINT 'You must specify a value for the firstname or lastname' ELSE IF @r_Code = 2 PRINT 'You must specify a value for the phone number' ELSE IF @r_Code = 3 PRINT 'You must provide all address information, Street address, City, State and Zipcode' ELSE IF @r_Code = @@IDENTITY SELECT [Customer ID] = 'The new customer ID is: ' + CONVERT(CHAR(2), @r_Code)

The stored procedure returns a message stating that the record already exists.

Change the value of @FirstName to Jeff and @LastName to Fellinge, then execute the query again.

The stored procedure returns a message with the new customer ID.

Lesson Summary

Important programming features available to stored procedures include parameters, variables, return codes, functions, default values, nesting, and cursors. Input parameters pass data to a stored procedure upon execution. Output parameters receive data from the stored procedure. Variables are used to hold values from output parameters. You specify an output parameter by using the OUTPUT keyword. You specify a variable by using the DECLARE keyword. Return codes are primarily used for error handling. Return codes are supplied by using the RETURN keyword, followed by an integer. When the RETURN statement is called, the stored procedure code exits unconditionally. If an integer is supplied in the RETURN statement, it is sent to a variable when the procedure is executed. The variable is set as equal to the stored procedure. Then, conditional programming language is used to match the return code with a task in the program. The @@ERROR function is combined with return codes in the stored procedure to capture database errors. Some errors can be avoided by setting default values for parameters.

You should write stored procedures to complete a single task. You can then execute one procedure from another procedure. Executing one procedure from another is called nesting. A stored procedure that calls itself is known as recursion. Procedures can be nested up to 32 levels deep. A powerful feature of SQL Server 2000 is returning records from the database via cursors. Cursors can be created in a stored procedure and called from an executing program. You should use cursors only when absolutely necessary, because if they are misused, they can severely impact database and network performance.