Creating and Using Stored Procedures

A stored procedure is a set of compiled T-SQL statements. Although these statements can return a result set just like a view, stored procedures offer many more capabilities. At the most basic level, you can use an ORDER BY clause in a stored procedure without also having to specify a TOP clause for a SELECT statement. Stored procedures enable basic programming capabilities, such as allowing IF ELSE statements and parameters to facilitate the reuse of code. Parameters are particularly useful for stored procedures that maintain databases; parameters can enable the inserting, updating, and deleting of table records. You can also perform data definition with stored procedures. For example, you can create tables.

Because stored procedures are compiled T-SQL statements, they are ideal for increasing the performance of your database administration tasks . Instead of having to compile T-SQL statements to perform a task, SQL Server can immediately process the compiled code. Because you can specify the code with parameters, it's possible to modify the behavior of stored procedures through the values that you assign to their parameters at run time.

Stored procedures offer a variety of ways to accept input and return values. The input parameters for stored procedures work much like the parameters for stored queries for traditional database files. However, SQL Server parameters readily permit the return of values from a stored procedure. Of course, a stored procedure can return a result set. In fact, it can return multiple result sets. You also can pass back values from a stored procedure by using output parameters. These parameters can handle the return of scalar values instead of result sets. Additionally, you can use the RETURN statement in a stored procedure to pass back an integer value to the procedure that invokes it. The syntax rules for RETURN enable the use of a constant or an expression. Output parameters can return all kinds of data types ”not just integer values. You will typically use an expression to specify an output parameter's value, but your T-SQL code can also designate a constant.

The Access UI for Stored Procedures

Access 2003 offers a couple of user interfaces for specifying stored procedures. You can view the different options by selecting Queries in the Objects bar in the Database window and clicking New. The New Query dialog box shows two options for creating a stored procedure. One option has the name Create Text Stored Procedure. The stored procedure template associated with this option facilitates typing T-SQL code into it. The template's layout and operation facilitate the creation and maintenance of stored procedures. Design Stored Procedure is the other option in the New Query dialog box for creating a stored procedure. This option presents a visual designer that is similar to the one you use for views. This designer is appropriate when you want to develop a result set from a single SELECT statement without any other T-SQL statements. This graphical designer is also available for stored procedures that insert, update, and delete records. However, it is not appropriate for stored procedures that perform data definition tasks, such as creating a new table or altering an existing one.

The two options for creating stored procedures are also available for maintaining them. To view the graphical or text-based version of an existing stored procedure, select the stored procedure in the Database window. Then click Design. If you open a stored procedure that returns a result set based on a single SELECT statement, you will see the visual designer for the stored procedure. If you open a procedure based on another kind of stored procedure (for example, one with more than a single SELECT statement), the text-based stored procedure template appears. Even if Access opens the visual designer view for a stored procedure, you can navigate to the text-based stored procedure template with the View drop-down menu on the toolbar. Although stored procedures with a single SELECT statement let you navigate freely between the two views, other kinds of stored procedures that open initially in the text-based template cannot be represented by the visual designer.

Note  

You can run a stored procedure by selecting it in the Database window and clicking Open. The Open button used to have the label Run In Access 2000. However, because Access 2003 offers a single mode for displaying views, stored procedures, and user-defined functions, you now click Open to run a stored procedure.

You can also manipulate stored procedures programmatically. This is more flexible and compatible with Books Online. For example, when you choose Create Text Stored Procedure, Access opens the stored procedure template with the CREATE PROCEDURE keyword phrase. After you initially save the stored procedure, it always opens with an ALTER PROCEDURE keyword phrase. This change in the keyword phrase makes it easy to modify a stored procedure, but the approach is at odds with most samples in Books Online. These samples typically drop the previously existing procedure and then execute a new CREATE PROCEDURE statement. When you create a stored procedure from a VBA module (as I will show you in subsequent samples in this section), you can follow the design guidelines in Books Online. In addition, creating the SQL Server stored procedure in a VBA procedure gives you more flexibility in using the stored procedure. For example, you can fire the stored procedure immediately after creating it. Whether you create the stored procedure with VBA or from the Access stored procedure template, you can invoke the stored procedure from an event procedure, such as a procedure that runs after a user clicks a button.

Using the LIKE Operator with datetime Column Values

Figure 11-18 shows the Design view for a stored procedure named Orders_in_like_1996 . It uses the LIKE operator to specify a criterion for OrderDate column values. The rows with OrderDate column values that include 1996 pass through the filter for membership in the stored procedure's result set. Recall that SQL Server represents datetime values internally as numbers , but it displays and filters them as string values. This feature makes it possible to use the LIKE operator to filter rows based on their column values.

Figure 11.18: The Access 2003 visual designer for stored procedures.

As you can see, the visual designer for stored procedures has the same look and feel as the visual designer for views. Both designers enable the specification of a result set based on a single SELECT statement. However, one designer creates and maintains a stored procedure object, and the other manages a view. The visual designer for stored procedures offers special features, such as the ability to represent INSERT , UPDATE , and DELETE statements. Although this chapter describes and illustrates how to program these statements, you can examine the programming samples with the visual designer to see how the designer handles INSERT , UPDATE , and DELETE .

Although most stored procedures such as the one shown in Figure 11-18 open to the visual designer by default, you can switch to the text-based template. Click the View down arrow on the toolbar and choose SQL View. This capability to switch between views lets you begin designing a stored procedure in the visual designer and then refine it in the text-based designer. Figure 11-19 presents a second stored procedure that filters the Orders table for rows with an order date occurring in 1997.

Figure 11.19: The Access 2003 text-based designer for stored procedures.

The text-based designer has a look and feel that Access developers who aren't familiar with SQL Server might find foreign. If you are familiar with SQL Server, you might notice that this designer is somewhat similar to the Query Analyzer. The sample in Figure 11-19 starts with ALTER PROCEDURE , followed by the stored procedure's name, because the sample is based on a previously created stored procedure. On your initial attempt to create a stored procedure, the first line reads CREATE PROCEDURE "StoredProcedurex" , where x is a number such as 1, 2, or whatever. You can overwrite this default name with one that is more meaningful for your application.

The next group of lines before the AS keyword occurs within block comment markers (/* and */). The template includes these lines in comments to remind you of the format and location for parameters. The parentheses for parameters within the comment markers are optional. Subsequent samples in this section discuss and demonstrate the syntax for specifying parameters.

The T-SQL syntax after the AS keyword specifies the operation and the result set from a stored procedure. The text-based stored procedure template gives you more precise control over the layout and formatting of a stored procedure's T-SQL code than the visual designer's SQL pane. For example, in the text-based stored procedure template, you can drop the dbo qualifier for Orders in the FROM clause if it's not necessary. You can also insert comment lines. Use two contiguous hyphens (--) at the start of a line to mark it as a comment.

Specifying Parameters in the Text-Based Designer

The preceding two samples show how to extract all rows from the Orders table for a particular year. Without parameters, you're forced to create a separate stored procedure for each year for which you want to filter. The code sample that follows shows how to filter the rows for any year at run time. Instead of using the LIKE operator, this Orders_in_@year stored procedure relies on the DATEPART function to extract just the year from the OrderDate column value. Specifying the function extracts a four-digit field. Therefore, the sample specifies an int data type for the parameter. The four y 's in the DATEPART mean that instead of using, say, 96, you must use 1996.

 ALTERPROCEDUREOrders_in_@year @yearint AS SELECTOrderID,OrderDate,ShipVia,Freight FROMOrders WHEREDATEPART(yyyy,OrderDate)=@year 

When you select the Orders_in_@year stored procedure in the Database window and click Open, Access displays a dialog box for you to type a parameter value into. (See Figure 11-20.) You are prompted for the parameter name that follows the @ sign in the stored procedure. Therefore, the prompt for the parameter in Orders_in_@year is for the year. This is how a typical parameter query from an Access database file would work.

Figure 11.20: A prompt for a stored procedure parameter value.

The preceding sample requires you to input a value each time you run the stored procedure. However, sometimes it's useful to have a default value for a parameter so that users have the opportunity to omit specifying one. By specifying a default value for a parameter, users can either accept the default value or override it with another one that they input.

The following code sample shows the syntax for designating a default value for a parameter. The Orders_in_@year_96default stored procedure shows how to assign a value in the parameter's declaration. Essentially, an assignment statement is all you need.

 ALTERPROCEDUREOrders_in_@year_96default @yearint=1996 AS SELECTOrderID,OrderDate,ShipVia,Freight FROMOrders WHEREDATEPART(yyyy,OrderDate)=@year 

If you run this stored procedure from the Database window by selecting Orders_in_@year_96default and clicking Open, the result set always contains rows for 1996. No prompt appears for a parameter with a default value. To override the default setting, you must invoke the stored procedure from another procedure that explicitly passes an alternative parameter value to the stored procedure's default setting. The following sample demonstrates this approach by passing a parameter of 1997 to override the default value of 1996:

 ALTERPROCEDUREExec_96default_with_1997 AS EXECOrders_in_@year_96default1997 

Sorting Result Sets

Although you can sort the result set from a view, it's probably more natural to sort the result set from a stored procedure. There are a couple of reasons for this. First, the syntax is more straightforward in stored procedures. Recall that you do not need to include a TOP clause in a SELECT statement. Second, you can make the sort order dynamic by assigning it at run time through a parameter. This capability is unavailable with a view.

Figure 11-21 presents two Design views. The top window shows a view, and the other shows a stored procedure. Both the view and the stored procedure sort the Orders table rows by freight in descending order. The top window, for the view, shows the TOP clause. Notice that you have to choose the top 100 percent ”namely all the rows in the result set. It doesn't make sense to do this other than for the fact that the syntax requires this formulation to enable sorting within a view. With the stored procedure, you can simply specify an ORDER BY clause for the columns on which you need to sort a result set. In addition, the stored procedure formulation (shown in the next sample) lends itself to an extension that isn't possible with a view.

Figure 11.21: A comparison of two Design views that sort records: one for a view, and one for a stored procedure.

The following stored procedure illustrates an extension of the sample shown in the bottom window of Figure 11-21. This extension lets a user control the sort order at run time. The default calls for an ascending sort on Freight . However, if a user assigns a value of 1 to the parameters for the stored procedure, the code sorts on Freight in descending order. As you can see, the design of the Orders_sorted_by_Freight_Up_Or_Down stored procedure uses an IF ELSE statement to branch to one of two blocks of code. BEGIN and END keywords mark the start and conclusion of each block. You need to repeat the code once for a descending order sort and a second time for an ascending order sort because of the T-SQL syntax. You can't specify the sort order ( DESC or ASC ) with a parameter.

 ALTERPROCEDUREOrders_sorted_by_Freight_Up_Or_Down @zabit=0 AS     IF@za=1 BEGIN SELECTOrderID,OrderDate,Freight FROMdbo.Orders ORDERBYFreightDESC END ELSE BEGIN SELECTOrderID,OrderDate,Freight FROMdbo.Orders ORDERBYFreightASC END 

You can use an Access application to invoke this SQL Server stored procedure and to return the values in its result set. The trick is to assign a value to the @za parameter. There are three basic steps to this process. First, you point an ADO Command object at the stored procedure. You need a Command object instead of a Connection object, because the approach requires you to set parameter values and a Connection object does not have a Parameters collection. Second, you need to create the parameter for @za , assign it a value, and append it to the Command object that points at the stored procedure. Third, you need to execute the stored procedure referenced by the Command object and print a subset of its records to confirm the order of the sort.

Note  

The section on user-defined functions at the end of the chapter illustrates how to run T-SQL statements that require parameters without using Command objects. Although this alternative syntax is generally simpler, it forfeits the ability to specify the data type for an input parameter. Once you know how to execute both approaches, you can make an informed decision about which is appropriate for you.

The following pair of VBA procedures illustrates the syntax to run the Orders_sorted_by_Freight_Up_Or_Down stored procedure. The first procedure points a Command object at the stored procedure and assigns a parameter to the command. This parameter sets a value for the @za parameter and passes it to the stored procedure when the code executes the command. Notice that the command's CommandType property is adCmdStoredProc . This is necessary when running a stored procedure from a command. The CommandText property designates the name of the stored procedure. The first procedure calls the second procedure and passes it the Command object, signifying the number of records to print. The second procedure executes the command, passes a subset of the records in the result set to a Recordset object, and prints the values to the Immediate window.

 SubAssignFreightSortOrder()  Dimcmd1AsADODB.Command Dimprm1AsADODB.Parameter     'Instantiatecommandandsetupforusewithstoredproc, 'andcreateparameter,assignvalue,andappendtocommand Setcmd1=NewADODB.Command Withcmd1 .ActiveConnection=CurrentProject.Connection .CommandType=adCmdStoredProc .CommandText= "Orders_sorted_by_Freight_Up_Or_Down" Setprm1=_ .CreateParameter("@za",adBoolean,adParamInput) prm1.Value=_ InputBox("Enter1forDescendingor0for " &