Managing Data Manipulation Using Stored Procedures

• Manage data manipulation by using stored procedures.

The first half of this chapter is devoted to understanding how stored procedures work. You'll learn what stored procedures are, how they are created and managed, how they are executed, why they are so important, and finally some error handling strategies for stored procedures.

What Is a Stored Procedure?

According to SQL Server Books Online, a stored procedure is "a group of Transact-SQL statements compiled into a single execution plan." What does that mean? Well, it's a group of Transact-SQL statements, which is similar in nature to the idea of a batch discussed in Chapter 6. It's compiled into an execution plan, which means it can be compiled once and the execution plan may be reused, which reduces overhead somewhat.

Guessing from the name , a stored procedure is probably, well, stored somewhere. In this case, a stored procedure is actually stored in a databaseany database you want. Specifically, if you're really interested, stored procedures are stored in the syscomments system table in each database. This table contains the source codethe T-SQLthat you write to create the stored procedure.

A stored procedure can have input parameters, which make it possible to tell the stored procedure things, such as the parameters for the queries it should run. A stored procedure can return results in several ways: with an integer return code, with one or more resultsets, and with output parameters.

This is all very abstract. Take a look at how stored procedures are created; that should make things a bit more solid.

Creating and Managing Stored Procedures

Stored procedures are created using the aptly named CREATE PROCEDURE statement. The CREATE PROCEDURE statement has to be the first executable line in a batch, and the stored procedure continues until the end of the batch, which is until the end of the file or until the word GO . You can abbreviate the word PROCEDURE to just PROC . Here's an example:

 CREATE PROCEDURE TableList AS SELECT name FROM sysobjects WHERE type = 'u' GO 

This stored procedure returns the same thing all the time: the list of tables, which includes the objects of type 'u' , in the database in which the stored procedure was created. The name of the stored procedure is TableList . Stored procedure names follow the same rules as other object names . The keyword AS marks the beginning of the actual code to be executed. The end, of course, is marked with GO , which tells SQL Server that the batch is at a close and the stored procedure should be compiled.

That's not particularly a useful or helpful stored procedure; it's almost faster to type the entire query than it is to try and remember the stored procedure name. How about something a bit more interesting:

 CREATE PROCEDURE TableList2 @NamePattern sysname = null AS if @NamePattern is null SELECT name FROM sysobjects WHERE type = 'u' else SELECT name FROM sysobjects WHERE type = 'u' and name like '%' + @NamePattern + '%' GO 

Now you have a stored procedure that can do something almost helpful. The @NamePattern sysname = null looks like a variable declaration, but it's actually a parameter declaration. A parameter is a variable that is passed in to a stored procedure to give the stored procedure a little guidance on how to get things done.

NOTE

Variables and Datatypes The sysname variable type is frequently used when you're going to be handling object names, such as table names, column names, or database names, because it's the data type that SQL Server uses in system tables. It's the preferred type to use for this type of information because as Microsoft changes the specifications for what an object name can and cannot be, the sysname type changes also, which should cause fewer coding revisions if you move to a different version of SQL Server.

In this case, it's a parameter of type sysname . The = null part means that the parameter is optional, and if it's not specified it should be null. It's just as proper to do "= ''" to default it to an empty string, or any other arbitrary string that makes sense as a default. The only rule is that the default has to be the correct datatype and length to fit into the parameter. Notice that the AS keyword goes before the code starts, but after the parameter declarations. A stored procedure can have up to 2,100 parameters. A default can be any value, including NULL . It can contain the wildcards and pattern matching characters , such as asterisks and square brackets. All the parameters can have defaults, so you can call the stored procedure with no parameters if you want just the defaults.

The code in the example is fairly straightforward, if the @NamePattern is null, the stored procedure returns all the tables in the database; if the parameter has a value, it returns tables that contain the string that is contained in @NamePattern . Step by Step 9.1 shows you how to create the same stored procedure with Enterprise Manager:

STEP BY STEP

9.1 Creating a New Stored Procedure with Enterprise Manager

1. Open SQL Server Enterprise Manager and connect to the server to which you want to add a stored procedure. If you need to, you can register the server now.

2. View the containers inside the server by clicking the plus sign to the left of the server, as shown in Figure 9.1.

   Figure 9.1. These are the objects within the server in SQL Server Enterprise Manager.

   

3. Open the Databases container, again by clicking the plus sign to the left of the word Databases . Choose a database from the list and view its containers by clicking the plus sign to the left of the database you want to work with. If you're just playing with the examples in the book, use the Pubs database. Click the Stored Procedures container, as shown in Figure 9.2.

   Figure 9.2. These are stored procedures within the Pubs database.

   

4. To create a new procedure, right-click in the right pane and choose New Stored Procedure. The Stored Procedure Properties - New Stored Procedure window appears, as shown in Figure 9.3.

   Figure 9.3. This is the window you use to create new stored procedures.

   

5. Change [OWNER] and [PROCEDURE NAME] to the correct owner and name of the procedure. If you're unsure of the owner name, delete the [OWNER] and the period that follows it. Then, after the AS keyword, type in your stored procedure. An example is shown in Figure 9.4.

   Figure 9.4. This is an example stored procedure typed into the Stored Procedure Properties window.

   

6. Click on the Check Syntax button to make sure you typed everything properly. This should pop up a dialog box that says Syntax check successful !. Clear the dialog box by clicking OK.

7. Click OK to save your stored procedure.

8. To edit an existing stored procedure, just double-click on it. You'll have the same option to check syntax as before.

The stored procedures you've seen in this chapter so far provide output by creating an output record set. Stored procedures can also pass back return valuesintegers that usually relay status information to the caller. To pass back a number to the caller, use the RETURN statement. The RETURN statement immediately exits the stored procedure, so don't use it until the stored procedure is all done, like this:

 CREATE PROCEDURE TableList3        @NamePattern sysname = null AS if @NamePattern is null begin        SELECT name FROM sysobjects WHERE type = 'u'        RETURN 1 end else begin        SELECT name FROM sysobjects WHERE type = 'u' and name like '%' + @NamePattern + '%'        RETURN 0 end GO 

This returns a value of 1 if the resultset contains all the tables, or a zero if it is returning a filtered set. Provided that the calling application is expecting the return value and knows how to decipher it, return values can be very helpful for returning record counts, error messages, or anything else meaningful back to the calling application. All the system stored procedures, for example, return a value of for success and some non-zero result for failure. The RETURN statement can be used only to return integers.

In addition to return codes, stored procedures can also pass data to the calling application by using output parameters. Output parameters are declared like other parameters in the CREATE PROCEDURE statement with the word OUTPUT after them, like this:

 CREATE PROCEDURE TableList4        @NamePattern sysname = null,        @RowCT int OUTPUT AS if @NamePattern is null begin        SELECT name FROM sysobjects WHERE type = 'u'        SET @RowCT = @@ROWCOUNT        RETURN 1 end else begin        SELECT name FROM sysobjects WHERE type = 'u' and name like '%' + @NamePattern + '%'        SET @RowCT = @@ROWCOUNT        RETURN 0 end GO 

This enables the calling program to accept the number of rows returned in a variable. You can also use an output parameter of type cursor to return the rowset, like this:

 CREATE PROCEDURE TableList5 @NamePattern sysname = null, @RowCT int OUTPUT, @OutputCursor CURSOR VARYING OUTPUT AS if @NamePattern is null begin SET @OutputCursor = CURSOR FORWARD_ONLY STATIC FOR SELECT name FROM sysobjects WHERE type = 'u' SET @RowCT = @@ROWCOUNT OPEN @OutputCursor RETURN 1 end else begin SET @OutputCursor = CURSOR FORWARD_ONLY STATIC FOR SELECT name FROM sysobjects WHERE type = 'u' and name like '%' + @NamePattern + '%' SET @RowCT = @@ROWCOUNT OPEN @OutputCursor RETURN 0 end GO 

Notice that the OPEN statement absolutely has to be in the procedure, or the cursor does not return properly. There are a few additional rules with using cursors for output:

• If the cursor is declared as FORWARD_ONLY and opened, but none of the rows are fetched , the entire resultset will be returned.

• If the cursor is declared as FORWARD_ONLY , and rows are fetched, then the cursor will only contain unaccessed rows.

• If the cursor is declared as FORWARD_ONLY , and all the rows in the cursor are fetched, but the cursor is not closed, then the cursor will contain an empty recordset.

• If the cursor is closed, or if the cursor isn't opened in the first place, the cursor variable won't contain a cursor; it will be NULL .

• If the cursor is scrollable, all the rows in the resultset are returned.

If you're in doubt about the validity of the cursor variable, you can use the CURSOR_STATUS() function. This function returns different values depending on the condition of the cursor, provided that you know the type of cursor (local, global, or variable). The syntax is CURSOR_STATUS('<type>', '<variable>') . The single ticks are mandatory. For example, to diagnose the preceding cursor, you could use CURSOR_STATUS('variable', '@OutputCursor') . Table 9.1 shows what the CURSOR_STATUS function returns:

Table 9.1. CURSOR_STATUS Return Values

Return Value	Explanation
1	The cursor is allocated, open, and valid, and has at least one row if it is a static cursor, or it may have zero rows if it is dynamic.
	The cursor is allocated and open, but it is empty. This can happen only on static cursors.
-1	The cursor is closed.
-2	An error occurred, because the variable wasn't assigned or the cursor was closed and de-allocated.
-3	The cursor doesn't exist.

IN THE FIELD: USING CURSORS AS RETURNS

Cursors are not used as returns very often because there are better ways of dealing with returning rowsets, such as just returning the rowset or the temporary table method discussed in the section titled, "Stored Procedures and Temporary Tables." Even the new table data type as an output parameter is more likely to get used than a cursor as an output parameter.

If you go back and read the service pack notes for past versions of SQL Server, you'll notice that nearly every one references bugs that can cause access violations related to cursors. Also, cursor performance tends to be about an order of magnitude below that of set operations, because SQL Server was built from the ground up to handle sets, and it handles row-by-row operations with some hesitation. Finally, there are very few operations that you cannot do without using a cursor. Cursors are the last resort, to be used sparingly, if at all.

Stored Procedure Groups

When you create a set of stored procedures, you can also add them all into a group by specifying a number after the stored procedure namefor example:

 CREATE PROCEDURE TableList;1 

You can then create a whole bunch of procedures with the same name and different numbers. This is called a stored procedure group. For example, if you have several applications that all run in one database, you can assign them all the same name and different numbers . That way when the application is removed from the database, it's easy to remove the associated stored procedures.

Stored procedure groups is a feature that is not used very often because it hides the functionality of the stored procedure by keeping it out of the name of the stored procedure. However, it's part of the syntax for CREATE PROCEDURE , so you should know what it does.

WARNING

Don't Use Stored Procedure Groups! Some features discussed in this chapter aren't widely used, but have to be discussed because they may be on the test. Stored procedure groups are one of them. Don't use stored procedure groups in real life because you may drop procedures inadvertently and it hides the true nature of a stored procedure by forcing it to have an artificial name.