Creating and Executing Stored Procedures

To create a stored procedure, you need to give the procedure a unique name and then write the sequence of SQL statements to be included in the procedure. The following is the basic syntax for creating stored procedures:

 create proc [owner.]procedure_name[; number]  [[(]@parm_name datatype = default_value [output]   [, ... ] [)]] [with {recompile  encryption}] as  SQL Statements  [return [integer_status_value]] 

It is good programming practice to always end a procedure with the RETURN statement and to specify a return status other than 0 when an error condition occurs. Listing 28.1 shows a simple stored procedure.

Listing 28.1 A Stored Procedure That Returns Book Titles and the Names of the Authors Who Wrote Them

 CREATE PROCEDURE dbo.title_authors AS    SELECT a.au_lname, a.au_fname, t.title       FROM titles t INNER JOIN            titleauthor ta ON t.title_id = ta.title_id RIGHT OUTER JOIN            authors a ON ta.au_id = a.au_id RETURN 

NOTE

Any user who is a member of the sysadmin fixed server role will map to the special user in each database, called dbo. dbo will own any procedure created by a member of the sysadmin fixed server role.

However, if the user is not a member of the sysadmin fixed server role, but is a member of the fixed database role db_owner, the user will own stored procedures in the database. The exception is if the dbo owner name is explicitly specified when the procedure is created.

For example, a user, joe_developer, uses Windows authentication to connect to SQL Server. He has been granted access to the Pubs database and is assigned the db_owner role. He has not been assigned to the sysadmin fixed server role. If he were to create a procedure in the Pubs database called myproc , myproc would be owned by joe_developer, not dbo. To access this object, you would have to qualify the procedure name as joe_developer.myproc .

Needless to say, this can be quite confusing if developers are creating stored procedures as members of the db_owner database role but are not members of the sysadmin group . It can lead to multiple copies of a stored procedure being created with the same name but owned by different users. Managing the security on objects owned by different users can be quite a chore as well (see Chapter 15, "Security and User Administration").

Fortunately, there is an easy workaround for this problem. Just make sure the procedure name is fully qualified with the dbo user ID when it is created, as shown in the following example:

 create proc dbo.myproc  as select * from titles 

This procedure will now be owned by dbo.

To execute a stored procedure, simply invoke it by its name (the same way you probably have already executed system stored procedures, such as sp_help ). If the procedure isn't the first statement in a batch, precede the procedure name with the EXEC keyword. See Chapter 26, "Using Transact-SQL in SQL Server 2000," for more information on batches. The following is the basic syntax for executing stored procedures:

 [exec[ute]] [@status =] [owner.]procedure_name[; number]    [[@parm_name =] expression [output][, ... ]] [with recompile] 

NOTE

The reason for the EXEC keyword rule is quite simple. SQL Server breaks down the commands sent to it in a batch by searching for keywords. Stored procedure names aren't keywords. If SQL Server finds a procedure name among statements, chances are that SQL Server will return an error message because it tries to treat it as part of the preceding command. Sometimes the execution is successful, but SQL Server doesn't execute what you want:

 SELECT * FROM titles  sp_help 

The SELECT statement runs fine, but the procedure is not executed. The reason is that sp_help ends up being used as a table alias for the titles table in the SELECT statement.

If you precede the procedure name with EXEC , you will get the expected behavior:

 SELECT * FROM titles  EXEC sp_help 

Why don't you have to put EXEC in front of the procedure name if the procedure is the first statement in a batch? If SQL Server doesn't recognize the first string in a batch, it simply assumes that it is a name of a stored procedure. Execute the following string and notice the error message:

 Dsfdskgkghk  Server: Msg 2812, Level 16, State 62 Could not find stored procedure 'dsfdskgkghk'. 

As good programming practice, it is best to always precede stored procedures with the EXEC keyword ”this way, it will always work as expected whether it's the first statement in a batch or not.

Stored Procedure Groups

If you create a procedure with a name that already exists, you receive an error message that might not be expected. Let's try to create a procedure with the same name as a procedure that already exists, title_authors :

 CREATE PROCEDURE title_authors AS  SELECT a.au_lname, a.au_fname, t.title FROM titles t INNER JOIN  titleauthor ta ON t.title_id = ta.title_id RIGHT OUTER JOIN  authors a ON ta.au_id = a.au_id  WHERE a.state = 'CA' RETURN Server: Msg 2714, Level 16, State 5, Procedure title_authors, Line 7 There is already an object named 'title_authors' in the database. 

SQL Server does provide the capability of having multiple procedures created under the same name, but with different group numbers . To create a procedure with a different group number, add a semicolon ( ; ) and an integer as shown in Listing 28.2.

Listing 28.2 Creating a Stored Procedure with a Group Number

 CREATE PROCEDURE title_authors;2 AS SELECT a.au_lname, a.au_fname, t.title FROM titles t INNER JOIN  titleauthor ta ON t.title_id = ta.title_id RIGHT OUTER JOIN  authors a ON ta.au_id = a.au_id  WHERE a.state = 'CA' RETURN GO EXEC title_authors;2 

The first procedure created will automatically get an implicit group number of 1. If no group number is specified when executing a stored procedure, SQL Server will execute the stored procedure with group number 1 by default.

Stored procedure groups are an odd feature in SQL Server, and they have limited usefulness . While you can drop all procedures within a group by excluding the group number with the DROP PROCEDURE statement, you cannot drop a single procedure within a group. One potential use of grouping functionality is for version handling. For each new version of the procedure, you could add to the grouping number rather than creating a new procedure with a different name. The original stored procedure will still be available for clients that have not yet been modified to use the newer version of the stored procedure. Updated clients can execute the new version of the stored procedure by including the new group number.