Creating Stored Procedures

3 4

In this section, we'll look at three methods for creating a stored procedure: using the T-SQL CREATE PROCEDURE statement, using Enterprise Manager, and using the Create Stored Procedure Wizard. Whichever method you choose, make sure that you execute each procedure to test it, and then edit and re-create the procedure, as necessary, until it functions the way you want it to.

Using the CREATE PROCEDURE Statement

The basic syntax for the CREATE PROCEDURE statement is shown here:

 CREATE PROC[EDURE] procedure_name                    [ {@parameter_name data_type}] [= default] [OUTPUT] [,...,n] AS t-sql_statement(s) 

Let's create a simple stored procedure. This stored procedure will select (and return) three columns of data for each row in the Orders table with a date in the ShippedDate column later than the date in the RequiredDate column. Note that a stored procedure can be created in only the database that is currently being accessed, so we must first specify the database via the USE statement. Before we create the procedure, we will also determine whether a stored procedure with the name we want to use already exists. If one does exist, we will drop it, and then we will create a new procedure with the same name. The T-SQL code used to create this procedure is shown here:

 USE Northwind GO IF EXISTS (SELECT name FROM sysobjects WHERE name = "LateShipments" AND type = "P") DROP PROCEDURE LateShipments GO CREATE PROCEDURE LateShipments AS SELECT RequiredDate, ShippedDate, Shippers.CompanyName FROM Orders, Shippers WHERE ShippedDate > RequiredDate AND Orders.ShipVia = Shippers.ShipperID GO 

When you run this T-SQL code, the stored procedure will be created. To run the stored procedure, simply call it by name, as shown here:

 LateShipments GO 

The LateShipments procedure will return 37 rows of data. If the statement that calls the procedure is part of a batch of statements and is not the first statement in the batch, you must use the EXECUTE (which can be abbreviated as "EXEC") keyword with the procedure call, as in the following example:

 SELECT getdate() EXECUTE LateShipments GO 

You can use the EXECUTE keyword even if the procedure is run as the first statement in a batch or if it is the only statement you are running.

Using Parameters

Now let's add an input parameter to our stored procedure so that we can pass data into the procedure. To specify input parameters in a stored procedure, list the parameters, preceding each one with an @ symbol, as in @parameter_name. You can specify up to 1024 parameters in a stored procedure. For our example, we will create a parameter called @shipperName. When we then run the stored procedure, we enter a shipping company name, and the query will return rows for only that shipper. Here is the T-SQL code used to create the new stored procedure:

 USE Northwind GO IF EXISTS (SELECT name FROM sysobjects WHERE name = "LateShipments" AND type = "P") DROP PROCEDURE LateShipments GO CREATE PROCEDURE LateShipments @shipperName char(40) AS SELECT RequiredDate, ShippedDate, Shippers.CompanyName FROM Orders, Shippers WHERE ShippedDate > RequiredDate AND Orders.ShipVia = Shippers.ShipperID AND Shippers.CompanyName = @shipperName GO 

To run this stored procedure, you must supply an input parameter. If you don't, SQL Server will display an error message similar to the following:

 Procedure LateShipments, Line 0 Procedure 'LateShipments' expects parameter '@shipperName', which was not supplied. 

To return the qualifying rows for the shipper Speedy Express, run the following statement:

 USE Northwind GO EXECUTE LateShipments "Speedy Express" GO 

You will see 12 rows returned from this stored-procedure call.

You can also supply a default value for a parameter, which will be used if no parameter is entered in the procedure call. For example, to use the default of United Package for our stored procedure, change the procedure's creation code to look like this. (Only the CREATE PROCEDURE line has been changed.)

 USE Northwind GO IF EXISTS (SELECT name FROM sysobjects WHERE name = "LateShipments" AND type = "P") DROP PROCEDURE LateShipments GO CREATE PROCEDURE LateShipments @shipperName char(40) = "United Package" AS SELECT RequiredDate, ShippedDate, Shippers.CompanyName FROM Orders, Shippers WHERE ShippedDate > RequiredDate AND Orders.ShipVia = Shippers.ShipperID AND Shippers.CompanyName = @shipperName GO 

Now when you run LateShipments without providing an input parameter, the procedure will use the default, United Package, for @shipperName, and it will return 16 rows. Even when a default parameter has been defined, you can provide an input parameter, which will override the default.

To return the value of a parameter in a stored procedure to the calling program, use the OUTPUT keyword after the parameter name. To save the value in a variable that can be used in the calling program, use the OUTPUT keyword when calling the stored procedure. To see how this works, let's create a new stored procedure that selects the unit price for a specified product. The input parameter, @prod_id, will be a product ID, and the output parameter, @unit_price, will be the returned unit price. A local variable named @price will be declared in the calling program and will be used to save the returned value. Here is the code used to create the GetUnitPrice stored procedure:

 USE Northwind GO IF EXISTS (SELECT name FROM sysobjects WHERE name = "GetUnitPrice" AND type = "P") DROP PROCEDURE GetUnitPrice GO CREATE PROCEDURE GetUnitPrice @prod_id int, @unit_price money OUTPUT AS SELECT @unit_price = UnitPrice FROM Products WHERE ProductID = @prod_id GO 

You must declare the variable in the calling program before you can use it in the stored-procedure call. For example, in the following code, we first declare the @price variable and assign it the data type money (which must be compatible with the output parameter data type), and then we execute the stored procedure:

 DECLARE @price money EXECUTE GetUnitPrice 77, @unit_price = @price OUTPUT PRINT CONVERT(varchar(6), @price) GO 

The PRINT statement returns the value 13.00 for @price. Note that we used the CONVERT statement to convert the value of @price to the varchar data type so that we could print it as a string, as a character data type, or as a type that can be implicitly converted to character—which is required for the PRINT statement. Also, notice also that different names were used for the output variables in the stored procedure and in the calling program so that you could better follow the placement of the variables in the example and to show that the names can be different.

You can also specify an input value for an output parameter when you execute a stored procedure. This means that the value will be entered into the stored procedure, which can modify the value or use it for operations; then the value is returned to the calling program. To send an input value to an output parameter, simply assign a value to the variable in the calling program before you execute the procedure, or execute a query that retrieves a value into a variable and then send that variable to the stored procedure. Now let's take a look at using local variables within a stored procedure.

Using Local Variables Within Stored Procedures

As shown in the preceding section, the DECLARE keyword is used to create local variables. You must specify the local variable's name and data type when you create it, and you must precede the variable name with an @ symbol. When you declare a variable, its value is initially set to NULL.

Local variables can be declared in a batch, in a script (or calling program), or in a stored procedure. Variables are often used in stored procedures to hold values that will be tested in a condition statement and to hold values that will be returned by a stored-procedure RETURN statement. Variables in stored procedures are also frequently used as counters. The scope of a local variable in a stored procedure is from the point at which the variable is declared until the stored procedure exits. Once the procedure exits, that variable can no longer be referenced.

Let's look at an example of a stored procedure that contains local variables. This procedure inserts five rows into a table by using the WHILE loop construct. First we will create a sample table, mytable, and then we will create the stored procedure, InsertRows. The local variables we will use in the procedure are @loop_counter and @start_val, which we will declare together and separate by a comma. The following T-SQL code creates the table and the stored procedure:

 USE MyDB GO CREATE TABLE mytable ( column1 int, column2 char(10) ) GO CREATE PROCEDURE InsertRows @start_value int AS DECLARE @loop_counter int, @start_val int SET @start_val = @start_value - 1 SET @loop_counter = 0 WHILE (@loop_counter < 5) BEGIN INSERT INTO mytable VALUES (@start_val + 1, "new row") PRINT (@start_val) SET @start_val = @start_val + 1 SET @loop_counter = @loop_counter + 1 END GO 

Now let's execute this stored procedure with a start value of 1, as shown here:

 EXECUTE InsertRows 1 GO 

You'll see five values printed for @start_val: 0, 1, 2, 3, and 4. Select all rows from mytable by using the following statement:

 SELECT * FROM mytable GO 

After we execute this SELECT statement, the output looks like this:

 column1 column2 ----------- -------- 1 new row 2 new row 3 new row 4 new row 5 new row 

After the stored procedure is completed, the two variables @loop_counter and @start_val can no longer be accessed. You will receive an error message if you try to print them by using the following T-SQL statement:

 PRINT (@loop_counter) PRINT (@start_val) GO 

The error message will be similar to this:

 Msg 137, Level 15, State 2, Server JAMIERE3, Line 1 Must declare the variable '@loop_counter'. Msg 137, Level 15, State 2, Server JAMIERE3, Line 2 Must declare the variable '@start_value'. 

The same rules concerning the scope of a variable apply when you are running a batch of statements. As soon as the GO keyword is issued (which marks the end of the batch), any local variables declared within the batch are no longer accessible. The scope of the local variable is only within the batch. To better understand these rules, let's look at a stored-procedure call from an earlier example:

 USE Northwind GO DECLARE @price money EXECUTE GetUnitPrice 77, @unit_price = @price OUTPUT PRINT CONVERT(varchar(6), @price) GO PRINT CONVERT(varchar(6), @price) GO 

The first PRINT statement prints the @price local variable from within the batch. The second PRINT statement attempts to print it again outside the batch, but the statement will return an error message. The output will be similar to the following:

 13.00 Msg 137, Level 15, State 2, Server JAMIERE3, Line 2 Must declare the variable '@price'. 

Note that the first PRINT statement was successful. (It printed the value 13.00.)

You might want to use the BEGIN TRANSACTION, COMMIT, and ROLLBACK statements within a stored procedure that has more than one T-SQL statement. Doing so indicates which statements should be grouped as one transaction. See Chapter 19 for information about transactions and the use of these statements.

Using RETURN

You can return from any point in a stored procedure to the calling program by using the RETURN keyword to exit unconditionally from the procedure. RETURN can also be used to exit from a batch or statement block. When RETURN is executed in a stored procedure, execution stops at that point in the procedure and returns to the next statement in the calling program. The statements following RETURN in the procedure are not executed. You can also return an integer value by using RETURN.

First, let's look at an example of using RETURN to simply exit from a stored procedure. We will create a modified version of the GetUnitPrice procedure that checks whether an input value is provided and, if one is not, prints a message to the user and returns to the calling program. To do this, we will define the input parameter with a default value of NULL and will then see whether the value is NULL within the procedure, which would indicate that no value was entered. Here is the code to drop and re-create this procedure:

 USE Northwind GO IF EXISTS (SELECT name FROM sysobjects WHERE name = "GetUnitPrice" AND type = "P") DROP PROCEDURE GetUnitPrice GO CREATE PROCEDURE GetUnitPrice @prod_id int = NULL AS IF @prod_id IS NULL BEGIN PRINT "Please enter a product ID number" RETURN END ELSE BEGIN SELECT UnitPrice FROM Products WHERE ProductID = @prod_id END GO 

Now let's run GetUnitPrice without entering an input value and see the results. You must specify the EXECUTE statement to run the stored procedure because the statement that calls the procedure is not the first of this batch. Use the following code:

 PRINT "Before procedure" EXECUTE GetUnitPrice PRINT "After procedure returns from stored procedure" GO 

Your output will look like this:

 Before procedure Please enter a product ID number After procedure returns from stored procedure 

The second PRINT statement is included to demonstrate that when RETURN is executed from the stored procedure, the batch continues at the PRINT statement.

Now let's look at using RETURN to return a value to the calling program. The value returned must be an integer. It can be a constant or a variable. You must declare a variable in the calling program in order to store the return value for later use in the calling program. For example, this next procedure will return a value of 1 if the unit price of the product specified in the input parameter is less than $100; otherwise, it will return 99.

 CREATE PROCEDURE CheckUnitPrice @prod_id int AS IF (SELECT UnitPrice FROM Products WHERE ProductID = @prod_id) < 100 RETURN 1 ELSE RETURN 99 GO 

To call this stored procedure and to be able to make use of the return value, declare a variable in the calling program and set it equal to the return value of the stored procedure (using the ProductID value of 66 for the input parameter), as follows:

 DECLARE @return_val int EXECUTE @return_val = CheckUnitPrice 66 IF (@return_val = 1) PRINT "Unit price is less than $100" GO 

The results will show the statement "Unit price is less than $100" because the unit price for the specified product is $17 and because, therefore, the returned value is 1. Make sure that you specify an integer data type when you declare the variable used to hold the return value because RETURN requires the value to be an integer.

Using SELECT to Return Values

You can also return data from a stored procedure by using the SELECT statement within the procedure. You can return the result set from a SELECT query or return the value of a variable.

Let's work through a couple of examples. First we'll create a new stored procedure called PrintUnitPrice, which returns the unit price for the product specified (by its product ID) in the input parameter. The code is as follows:

 CREATE PROCEDURE PrintUnitPrice @prod_id int AS SELECT ProductID, UnitPrice FROM Products WHERE ProductID = @prod_id GO 

Call this procedure with 66 as the input parameter value, as shown here:

 PrintUnitPrice 66 GO 

Your results will look like this:

 ProductID UnitPrice ----------- ------------------------ 66 17.00 (1 row(s) affected) 

To return variable values by using the SELECT statement, use SELECT followed by the variable name. In the following example, we re-create the CheckUnitPrice stored procedure to return a variable value, and we specify an output-column heading:

 USE Northwind GO IF EXISTS (SELECT name FROM sysobjects WHERE name = "CheckUnitPrice" AND type = "P") DROP PROCEDURE CheckUnitPrice GO CREATE PROCEDURE CheckUnitPrice @prod_id INT AS DECLARE @var1 int IF (SELECT UnitPrice FROM Products WHERE ProductID = @prod_id) > 100 SET @var1 = 1 ELSE SET @var1 = 99 SELECT "Variable 1" = @var1 PRINT "Can add more T-SQL statements here" GO 

Call this procedure with 66 as the input parameter value, as shown here:

 CheckUnitPrice 66 GO 

The output from running this stored procedure looks like this:

 Variable 1 ----------- 99 (1 row(s) affected) Can add more T-SQL statements here 

We printed the statement "Can add more T-SQL statements here" to see the difference between returning a value by using SELECT and returning a value by using RETURN. RETURN will end the stored procedure wherever it is called, but SELECT will return its result set, and the stored procedure will then continue to execute.

In the previous example, if we had not specified an output-column heading and had simply used SELECT @var1 instead, we would have seen the output with no heading, as follows:

 ----------- 99 (1 row(s) affected) 

Using Enterprise Manager

Now that you know how to use T-SQL to create stored procedures, let's look at how to use Enterprise Manager to create them. To create a stored procedure by using Enterprise Manager, you still must know how to write the T-SQL statements. Enterprise Manager simply provides you with a graphical interface in which to create your procedure. We'll practice this method by re-creating the InsertRows stored procedure, as explained in the following steps:

1. To delete the stored procedure, first expand the MyDB database folder in the left pane of Enterprise Manager and click the Stored Procedures folder. All stored procedures in that database will appear in the right-hand pane. Right-click the InsertRows stored procedure (it should exist already—we created it earlier in this chapter) and choose Delete from the shortcut menu. (You can also rename or copy the stored procedure through this shortcut menu.) The Drop Objects dialog box appears, as shown in Figure 21-1. Click Drop All to delete the stored procedure.

   

   Figure 21-1. The Drop Objects dialog box.

2. Right-click the Stored Procedures folder and choose New Stored Procedure from the shortcut menu. The Stored Procedure Properties window appears, as shown in Figure 21-2.

   

   Figure 21-2. The Stored Procedure Properties window.

3. In the Text box on the General tab, replace [OWNER].[PROCEDURE NAME] with the name of the stored procedure—in this case, InsertRows. Then type the T-SQL code for the stored procedure. Figure 21-3 shows the Stored Procedure Properties window after the T-SQL code for InsertRows has been added.

   

   Figure 21-3. The T-SQL code for the new stored procedure.

4. Click Check Syntax to have SQL Server point out any T-SQL syntax errors in the stored procedure. Correct any syntax errors found, and click Check Syntax again. Once the syntax check is successful, you will see the message box shown in Figure 21-4. Click OK.

   

   Figure 21-4. The message box indicating that the stored-procedure syntax check was successful.

5. Click OK in the Stored Procedure Properties window to create your stored procedure and to return to Enterprise Manager. Click the Stored Procedures folder in the left-hand pane of Enterprise Manager to display the new stored procedure in the right-hand pane, as shown in Figure 21-5.

   

   Figure 21-5. The new stored procedure in Enterprise Manager.

6. To assign execute permission for the new stored procedure to users, right-click the stored-procedure name in the right-hand pane of Enterprise Manager and choose Properties from the shortcut menu. In the Stored Procedure Properties window that appears, click Permissions. The Object Properties window appears, as shown in Figure 21-6. Select the boxes in the EXEC column for the users or database roles that you want to allow to execute this stored procedure. In this example, three users have been given execute permission for the InsertRows stored procedure.

   

   Figure 21-6. The Permissions tab of the Object Properties window.

7. Click Apply and then click OK to set the permissions you selected and to return to the Stored Procedure Properties window. Click OK to finish.

You can also use Enterprise Manager to edit a stored procedure. To do so, right-click the name of the procedure and choose Properties from the shortcut menu. Edit the procedure in the Stored Procedure Properties window (the same window shown in Figure 21-3), check the syntax by clicking Check Syntax, click Apply, and then click OK.

Additionally, you can use Enterprise Manager to manage permissions on a stored procedure. To do so, right-click the stored-procedure name in Enterprise Manager, point to All Tasks in the shortcut menu, and then choose Manage Permissions. You can also create a publication for replication (described in Chapter 26), generate SQL scripts, and display dependencies for the stored procedure from the All Tasks submenu. If you choose to generate SQL scripts, SQL Server will automatically create a script file (with the name you specify) that will contain the definition of the stored procedure. Then you can re-create the procedure, as necessary, by using the script.

Using the Create Stored Procedure Wizard

The third method for creating stored procedures, using the Create Stored Procedure Wizard, gives you a head start by providing a skeleton of T-SQL code for you to build on when writing your procedures. You can use the wizard to create a stored procedure to insert, delete, or update table rows. The wizard does not help with procedures that retrieve rows from a table.

The wizard lets you create multiple stored procedures in one database without exiting and restarting the wizard. However, to create a procedure in a different database, you must run the wizard again. To run the wizard, follow these steps:

1. In Enterprise Manager, choose Wizards from the Tools menu to display the Select Wizard dialog box. Expand the Database folder and select Create Stored Procedure Wizard, as shown in Figure 21-7.

   

   Figure 21-7. The Select Wizard dialog box.

2. Click OK to display the Create Stored Procedure Wizard welcome screen, shown in Figure 21-8.

   

   Figure 21-8. The Create Stored Procedure Wizard welcome screen.

3. Click Next to display the Select Database screen. Select the name of the database in which you want to create the stored procedure.
4. Click Next to display the Select Stored Procedures screen, shown in Figure 21-9. Here you will see a list of all the tables in the selected database, with three columns of check boxes. These columns represent the three types of stored procedures you can create using the wizard: stored procedures that insert, delete, or update data. Select the appropriate check boxes in the columns next to each table name.

   

   Figure 21-9. The Select Stored Procedures screen.

   This example shows two tables that have been used throughout this book. As you can see, the Bicycle_Inventory table has been assigned two procedures: an insert procedure and an update procedure. As shown in the subsequent steps, you will be able to modify the procedures before they are actually created.

   NOTE

   ---

   One stored procedure can perform multiple data modifications, but the Create Stored Procedure Wizard starts each modification type as a separate stored procedure. You can alter any of the procedures the wizard sets up by adding more T-SQL code.

5. Click Next to display the Completing The Create Stored Procedure Wizard screen, shown in Figure 21-10. This screen lists the names and descriptions of all stored procedures that will be created when you finish the wizard.

   

   Figure 21-10. The Completing The Create Stored Procedure Wizard screen.

6. To rename and edit a stored procedure, begin by selecting its name in the Completing The Create Stored Procedure Wizard screen and then clicking Edit to display the Edit Stored Procedure Properties window, shown in Figure 21-11. The window includes a list of the columns in the table that the procedure will affect. The columns that have a check mark in the Select column will be used in the stored procedure.

   

   Figure 21-11. The Edit Stored Procedure Properties window.

   This example shows six columns in the Bicycle_Inventory table that can be affected by the insert procedure, which is currently named insert_Bicycle_Inventory_1. Each table column has a check mark in the Select column. The check marks indicate that values for all six columns will need to be entered into the stored procedure when it is executed and that the stored procedure will insert the six values into all six columns.

7. To rename the stored procedure, delete the existing name in the Name text box, and replace it with the new name.
8. To edit the stored procedure, click Edit SQL to display the Edit Stored Procedure SQL dialog box, shown in Figure 21-12. Here you can view the T-SQL code for the stored procedure. As you can see, the T-SQL here is quite basic. In this example, the five parameters you list when you call the stored procedure will be the values inserted as the new row in the table. To edit the code, simply type your changes in the text box. When you have finished your edits, click Parse to check for any syntax errors, correct any errors, and then click OK to return to the Completing The Create Stored Procedure Wizard screen.

   

   Figure 21-12. The Edit Stored Procedure SQL dialog box.

9. After making your changes, if any, and verifying the code, click Finish to create the stored procedures. Don't forget to set permissions on each of the stored procedures after the procedures are created. (See the section "Using Enterprise Manager" earlier in the chapter for instructions on setting permissions.)

As you can see, the wizard is not extremely helpful. If you know how to write the T-SQL code, you might as well use scripts or Enterprise Manager to create your stored procedures.