Creating Stored Procedures

Many companies employ SQL Server developers full time or as consultants to do nothing else but code stored procedures. No matter whether you are the IT Manager or a DBA in charge of a mob of “proc-programmers” or a one-person show responsible for everything, the stored procedure deployment plan is one of the most important plans discussed in this book.

Stored Procedure Deployment

The following steps, illustrated in the flow chart in Figure 14–3, document the process of stored procedure creation and deployment from beginning to end. Create your own deployment plan, which will be your checklist that will take you from concept to deployment in a logical, well-controlled manner.

Figure 14–3: Executing the stored procedure

• Step 1: Obtain the stored procedure requirements   The requirement specs are obtained from the stored procedure plan as discussed earlier.

• The following specification is an example of a stored procedure requirement on an order-taking system that debits stock items from the inventory or warehouse table and credits them to the customer’s account.

• Step 2: Craft stored procedure logic or solution in pseudocode   You should work on this section with the idea of sketching the scope, functionality, and final result of the procedure.

In the preceding example, the pseudocode could be as follows:

 Declare variables of type Int

• Step 3: Model, write, and test the stored procedure against your development system    This work is done in Management Studio or the IDE of your choice (see Chapter 4). Before you begin code, however, stored procedures should be defined in a modeling language, which would aim to capture procedure-related metadata.

• Step 4: Deploy the stored procedure to the target system   Deployment of the stored procedure entails executing the CREATE PROCEDURE statement against the target system.

• Step 5: Encrypt the stored procedure   The same motivation used to justify trigger encryption applies here. Just as with triggers, this step is done inside the CREATE PROCEDURE statement. You must not encrypt your trigger in the development system unless you have a separate and secure version of the source code elsewhere. The WITH ENCRYPTION clause in the CREATE PROCEDURE statement is like a loaded Uzi with the safety off. One slip and off goes your foot. Be sure that you are connected to the target product system to install and encrypt the procedure. More on encryption later in this chapter.

• Step 6: Verify permissions   Permission verification is the last step you take before allowing users to obtain service from your stored procedure. Unlike with triggers, users must have direct permission to execute a stored procedure. This can be done in T-SQL code as explained in Chapter 5.

• However, the permissions issue does not stop with the right to call EXEC. You also need to verify that users on the connection that call the proc-either to query the table or to insert, update, or delete from it-have these DML permissions as well.

Creating a Stored Procedure UsingT-SQL

You can use any of several methods to create stored procedures; there are a few tools floating around. The principal method, of course, is to use the T-SQL statements CREATE PROC or CREATE PROCEDURE. You can also use the SQL-SMO object model to create, alter, and manage stored procedures.

To code and test stored procedures in T-SQL, you will use a query window in Management Studio. The stored procedure’s CREATE and ALTER templates are useful and will save you some coding time. The following syntax represents the CREATE PROC statement (the full explanation and usage of the arguments are documented in SQL Server Books Online):

 CREATE {PROC PROCEDURE} [schema_name.] procedure_name [ ; number]     [ { @parameter [type_schema_name.] data_type}        [VARYING] [=default] [ [OUT [PUT]     ] [ ,…n] [ WITH <procedure_option> [ ,…n  ] [ FOR REPLICATION] AS { <sql_statement> [;] [ …n] |  <method_specifier> } ;] <procedure_option> ::=     [ ENCRYPTION ]     [ RECOMPILE ]     [ EXECUTE AS Clause ]  <sql_statement> ::= { [ BEGIN ] statements [ END ] } <method_specifier> ::= AS T-SQL statements [ …n ]

A stored procedure must be named. It is a good idea to name a stored procedure with a prefix (such as the acronym of a process or module, as in jrs_storedproc). However, you should not name your stored procedures using the sp_ prefix, because those are typically reserved for system, built-in, and user-defined stored procedures.

You can create one or more parameters in a stored procedure. The client in the execution statement that calls the stored procedure must supply the values for the parameters. If a stored procedure expecting a parameter value does not receive it, the stored procedure will fail and return an error. It is thus especially important when you code stored procedures that you handle all parameter errors properly. You can code flow-control statements, return codes, and the like, as long as errors raised in the stored procedure are properly handled.

Ownership Referencing Inside Stored Procedures

Object names get resolved when a stored procedure is executed. If you reference object names inside a stored procedure and do not reference the object by ownership (name qualification), the ownership defaults to the owner of the stored procedure, and the stored procedure is thus restricted to the stored procedure owner. In other words, only the owner gets to execute the procedure.

Also, objects referenced by DBCC, ALTER TABLE, CREATE TABLE, DROP TABLE, TRUNCATE TABLE, CREATE INDEX, DROP INDEX, and UPDATE STATISTICS must also be qualified with the object owner’s name so that users can execute the stored procedure. If you create a stored procedure and do not reference any table objects in the stored procedure with qualified names, then access to the tables, during execution of the stored procedure, is restricted to the owner of the stored procedure (see how permissions affect this in Chapters 5).

Encryption

As discussed in the trigger section and in the stored procedure deployment plan, you can hide the code of your stored procedure using encryption (by using the WITH ENCRYPTION clause) as you do when you create triggers. However, once the procedure is encrypted, there is no way to decrypt it; not even the SA account or an Administrator can do so. Encrypting the code is a good idea if the code you have defined in the stored procedure exposes highly sensitive data, as long as you keep a copy of the unencrypted code that only you can access. Although it has never happened to me, I did hear of someone who spent a month writing the mother of all procedures and then encrypted the code on the development system by mistake, before he made a copy of the final source.

Encryption is also useful for a turnkey product that ships with the SQL Server engine as the data store. Your product will then be in the hands of third parties, and you’ll have no means of preventing them from checking out, tampering with, and even stealing the data store code. Encryption prevents all that. In fact if the product, such as a voice mail system, cannot operate without the data store, an encrypted stored procedure might obviate the need for one of those clumsy “dongles” you shove onto the parallel port to control access to the system, or prevent it from being pirated.

Grouping

You can create a group of stored procedures, each one having the same name to identify them as part of a group, by assigning each stored procedure in the group an identification number. Grouping the procedures like this allows you to maintain collections of stored procedures that pertain to a particular function or purpose. For example, I have a group of stored procedures that are all part of the accounts payable database, as follows:

 accpay; 1 accpay; 2 accpay; 3 accpay; 4…

Creating each member in the group is easy: Just specify the number of the individual procedure in your CREATE PROC code. Caveat? You cannot delete an individual. When you are done with the group, the statement DROP PROCEDURE destroys the whole group.

Creating, Testing, and Debugging a Stored Procedure

The steps you take to opening a CREATE PROCEDURE template or an ALTER PROCEDURE template are almost identical to what I described for triggers earlier, so forgive me if I don’t repeat those steps here. Let’s instead go directly to debugging the stored procedure, which means you need to open Visual Studio 2005.

Once the proc has been written and your syntax is clear of errors, the procedure code is ready to be observed in the debugger. As you step through the code, you can see parameters change and statements executed without affecting underlying table data.

Connect to the server holding your stored procedures. Drill down to the Programmability folder and expand the list of procedures. Double-click the proc or select OPEN from the context menus so that the procedure code window opens in Visual Studio. You can then set break points in the window as you would any .NET code.

1. Right-click the procedure and select Step Into Stored Procedure from the context menus. Select Debug from the context menu; the Run Procedure dialog box loads to allow you to enter parameter values to test with. This is demonstrated in Figure 14–4. (Notice the check box Auto Roll Back; enable this to roll back all changes made to the data while debugging a stored procedure).

   
   Figure 14–4: Debugging a stored procedure

2. Step into the code and watch the execution of each statement in the transaction. Two tables are operated on in this procedure, and both operations must complete or nothing must complete. So the code I am stepping through is enclosed in a transaction that I can roll back if I detect a failure anywhere in the transaction. (The full code of this stored procedure is listed later in this chapter, in the section “The Example.”) This illustrates that the initial queries have run and the local variables (see the parameters now for @Amt and @Debit) have been changed accordingly.

3. If I keep stepping through the code, as soon as I update either of the tables in this procedure the appropriate triggers will fire. We have left the procedure and entered the trigger code after the DML statement has been executed in the transaction. I can now step into the trigger. Notice the trigger (fishy) is now in the call stack. Depending on what the trigger is looking for, or to do, it might or might not cause the remainder of the procedure to execute in the debugger. Naturally, if you want to step through the proc without the intervention of the trigger, just drop the trigger from the table and reinstall it later.

If you change stored procedure (or trigger) code, you must execute the ALTER query to install the latest version of the procedure to the database. You are not editing the installed procedure when you run Edit or script out the procedure code, so failing to re-execute after changing the code will not help you, and you’ll be as confused as an apple in a peach tree when the bug you just squished returns to the debugger. Think of the Execute Query as the build or compile button on your traditional IDE.

It is also a good idea to save the query out to a text file or version control system as often as possible, because if you lose power or your system crashes before you have a chance to re-execute the ALTER query, the source code will be lost.