Now that you've learned about batches and transactions, it's time to examine another important capability of Transact -SQL. Stored procedures enable you to cache commands at the server for later use. To create a stored procedure, you take a batch and wrap it inside a CREATE PROCEDURE proc_name AS statement. After that, you use Transact-SQL with nothing special except for declaring which parameters will be passed to the procedure.
To demonstrate how easy it is to create a stored procedure, we've written a simple procedure called get_author that takes one parameter, the author_id , and returns the names of any authors that have IDs equal to whatever character string is passed to the procedure:
CREATE PROC get_author @au_id varchar(11) AS SELECT au_lname, au_fname FROM authors WHERE au_id=@au_id
This procedure can be subsequently executed with syntax like the following:
EXEC get_author '172-32-1176' or EXEC get_author @au_id='172-32-1176'
You can see that the parameters can be passed anonymously by including values in order, or the parameters can be explicitly named so that the order in which they're passed isn't important.
If executing the procedure is the first statement in the batch, using EXEC is optional. However, it's probably always best to use the keyword EXEC (or EXECUTE) so that you won't wind up wondering why your procedure wouldn't execute (only to realize later that it's no longer the first statement of the batch).
In practice, you'll probably want a procedure like the one above to be a bit more sophisticated. Perhaps you'd want to search for author names that begin with a partial ID that you pass. If you choose not to pass anything, the procedure should show all authors rather than give you an error message stating that the parameter is missing. And maybe you'd like to return some value as a variable (distinct from the result set returned and from the return code) that you would use to check for successful execution.
You can do this by passing an output parameter, which has a pass-by reference capability. Passing an output parameter to a stored procedure is similar to passing a pointer when calling a function in C. Rather than passing a value, you pass the address of a storage area in which the procedure will cache a value. That value is subsequently available to the SQL batch after the stored procedure has executed. For example, you might want an output parameter to tell you the number of rows returned by a SELECT statement. While the @@ROWCOUNT system function provides this information, it's available for only the last statement executed. If the stored procedure executed many statements, you'd need to put this value away for safekeeping. An output parameter provides an easy way to do this.
Here's a simple procedure that selects all the rows from the authors table and all the rows from the titles table. It also sets an output parameter for each table based on @@rowcount. This value is subsequently available to the calling batch by checking the variables passed as the output parameters.
CREATE PROC count_tables @authorcount int OUTPUT, @titlecount int OUTPUT AS SELECT * FROM authors SET @authorcount=@@ROWCOUNT SELECT * FROM titles SET @titlecount=@@ROWCOUNT RETURN(0)
The procedure would then be executed like this:
DECLARE @a_count int, @t_count int EXEC count_tables @a_count OUTPUT, @t_count OUTPUT
Variables declared in a stored procedure are always local, so we could have used the same names for both the variables in the procedure and those passed by the batch as output parameters. In fact, this is probably the most common way to invoke them. However, the point here is to make it clear that the variables don't need to have the same name. Even with the same name , they are in fact different variables because their scoping is different.
This procedure will return all the rows from both tables. In addition, the variables @a_count and @t_count will retain the row counts from the authors and titles tables, respectively.
SELECT authorcount=@a_count, titlecount=@t_count
Here's the output:
authorcount titlecount ----------- ---------- 23 18
When creating a stored procedure, you can reference a table, a view, or another stored procedure that doesn't currently exist. (In the latter case, you'll get a warning message informing you that a referenced object doesn't exist. As long as the object exists at the time the procedure is executed, all will be fine.)
Stored procedures can be nested and can call other procedures. A procedure invoked from another procedure can also invoke yet another procedure. In such a transaction, the top-level procedure has a nesting level of 1. The first subordinate procedure has a nesting level of 2. If that subordinate procedure subsequently invokes another stored procedure, the nesting level will be 3, and so on, to a limit of 32 nesting levels. If the 32-level limit is reached, a fatal error will result, the batch will be aborted, and any open transactions will be rolled back.
The nesting-level limit prevents stack overflows that can result from procedures recursively calling themselves infinitely. The limit allows a procedure to recursively call itself only 31 subsequent times (for a total of 32 procedure calls). To determine how deeply a procedure is nested at runtime, you can select the value of the system function @@NESTLEVEL.
Unlike it does with nesting levels, SQL Server has no practical limit on the number of stored procedures that can be invoked from a given stored procedure. For example, a main stored procedure could invoke hundreds of subordinate stored procedures. If the subordinate procedures don't invoke other subordinate procedures, the nesting level never reaches a depth greater than 2.
An error in a nested (subordinate) stored procedure isn't necessarily fatal to the calling stored procedure. When invoking a stored procedure from another stored procedure, it's smart to use a RETURN statement and check the return value in the calling procedure. In this way, you can work conditionally with error situations (as shown in the factorial example next ).
Stored procedures can perform nested calls to themselves, a technique known as recursion. Only powerful programming languages such as C and Transact-SQL support recursion. Recursion is a technique by which the solution to a problem can be expressed by applying the solution to subsets of the problem. Programming instructors usually demonstrate recursion by having students write a factorial program using recursion to display a table of factorial values for 0! through 10! . Recall that a factorial of a positive integer n , written as n! , is the multiplication of all integers from 1 through n . For example:
8! = 8 x 7 x 6 x 5 x 4 x 3 x 2 x 1 = 40320
(Zero is a special case ” 0! is defined as equal to 1.)
We can write a stored procedure that computes factorials, and we can do the recursive programming assignment in Transact-SQL.
Recursion isn't necessarily the best way to solve this problem. An iterative (looping) approach is probably better. This example is simply for illustrative purposes.
-- Use Transact-SQL to recursively calculate factorial -- of numbers between 0 and 12. -- Parameters greater than 12 are disallowed as result -- overflows the bounds of an int. CREATE PROC factorial @param1 int AS DECLARE @one_less int, @answer int IF (@param1 < 0 OR @param1 > 12) BEGIN -- Illegal parameter value. Must be between 0 and 12. RETURN -1 END IF (@param1=0 or @param1=1) SELECT @answer=1 ELSE BEGIN SET @one_less=@param1 - 1 EXEC @answer=factorial @one_less -- Recursively call itself IF (@answer= -1) BEGIN RETURN -1 END SET @answer=@answer * @param1 IF (@@ERROR <> 0) RETURN -1 END RETURN(@answer)
Note that when the procedure is initially created, a warning message like the one shown below will indicate that the procedure is referencing a procedure that doesn't currently exist (which is itself, in this case):
Cannot add rows to Sysdepends for the current stored procedure because it depends on the missing object 'factorial'. The stored procedure will still be created.
Once the procedure exists, we can use it to display the standard factorial table that students generate in C programming classes:
DECLARE @answer numeric, @param int SET @param=0 WHILE (@param <= 12) BEGIN EXEC @answer=factorial @param IF (@answer= -1) BEGIN RAISERROR('Error executing factorial procedure.', 16, -1) RETURN END PRINT CONVERT(varchar, @param) + '! = ' + CONVERT(varchar(50), @answer) SET @param=@param + 1 END
Here's the return table:
0! = 1 1! = 1 2! = 2 3! = 6 4! = 24 5! = 120 6! = 720 7! = 5040 8! = 40320 9! = 362880 10! = 3628800 11! = 39916800 12! = 479001600
We stopped at 12! in the factorial procedure because 13! is 6,227,020,800, which exceeds the range of a 32-bit (4-byte) integer. Even if we changed the parameter @answer to be type decimal , we would still be limited to 12! because the value included in a RETURN statement must be type int . Another version of the procedure is given here, which uses an output parameter for the answer and introduces a new variable to hold the returned status of the procedure. You can use a decimal datatype with a scale of 0 as an alternative to int for integer operations that require values larger than the 4-byte int can handle.
CREATE PROC factorial @param1 decimal(38,0), @answer decimal(38,0) output AS DECLARE @one_less decimal(38,0), @status int IF (@param1 < 0 OR @param1 > 32) BEGIN -- Illegal parameter value. Must be between 0 and 12. RETURN -1 END IF (@param1=0 or @param1=1) SET @answer=1 ELSE BEGIN SET @one_less=@param1 - 1 EXEC @status=factorial @one_less, @answer output -- Recursively call itself IF (@status= -1) BEGIN RETURN -1 END SET @answer=@answer * @param1 IF (@@ERROR <> 0) RETURN -1 END RETURN 0
Even though this procedure has removed the range limit on the result by using a decimal datatype to hold the answer, we can only go to 32! because we'd reach the maximum nesting depth of 32, which includes recursive calls. Allowing an initial parameter > 32 would result in the procedure terminating with error 217:
Maximum stored procedure nesting level exceeded (limit 32)
In C, you need to be sure that you don't overflow your stack when you use recursion. Using Transact-SQL shields you from that concern, but it does so by steadfastly refusing to nest calls more than 32 levels deep. You can also watch @@NESTLEVEL and take appropriate action before reaching the hard limit. As is often the case with a recursion problem, an iterative solution could be performed without the restriction of nesting or worries about the stack.
Here's an iterative approach. To illustrate that there's no restriction of 32 levels because it's simple iteration, we'll go two steps further, to 34! . So this version uses an output parameter declared as numeric(38,0) instead of int . (This example also illustrates that a numeric datatype with a scale of 0 can be used as an alternative to int for integer operations that require values larger than the 4-byte int can handle.) We stop at 34! because 35! would overflow the precision of a numeric(38,0) variable. (Note that SQL Server must be started with the -p flag to increase the maximum precision to 38 digits instead of the default of 28.) The iterative solution appears below.
-- Alternative iterative solution does not have the restriction -- of 32 nesting levels CREATE PROC factorial2 @param1 int, @answer NUMERIC(38,0) OUTPUT AS DECLARE @counter int IF (@param1 < 0 OR @param1 > 34) BEGIN RAISERROR ('Illegal Parameter Value. Must be between 0 and 34', 16, -1) RETURN -1 END SET @counter=1 SET @answer=1 WHILE (@counter < @param1 AND @param1 <> 0 ) BEGIN SET @answer=@answer * (@counter + 1) SET @counter=@counter + 1 END RETURN GO DECLARE @answer numeric(38, 0), @param int SET @param=0 WHILE (@param <= 34) BEGIN EXEC factorial2 @param, @answer OUTPUT PRINT CONVERT(varchar(50), @param) + '! = ' + CONVERT(varchar(50), @answer) SET @param=@param + 1 END
And here's the output table:
0! = 1 1! = 1 2! = 2 3! = 6 4! = 24 5! = 120 6! = 720 7! = 5040 8! = 40320 9! = 362880 10! = 3628800 11! = 39916800 12! = 479001600 13! = 6227020800 14! = 87178291200 15! = 1307674368000 16! = 20922789888000 17! = 355687428096000 18! = 6402373705728000 19! = 121645100408832000 20! = 2432902008176640000 21! = 51090942171709440000 22! = 1124000727777607680000 23! = 25852016738884976640000 24! = 620448401733239439360000 25! = 15511210043330985984000000 26! = 403291461126605635584000000 27! = 10888869450418352160768000000 28! = 304888344611713860501504000000 29! = 8841761993739701954543616000000 30! = 265252859812191058636308480000000 31! = 8222838654177922817725562880000000 32! = 263130836933693530167218012160000000 33! = 8683317618811886495518194401280000000 34! = 295232799039604140847618609643520000000
It's syntactically acceptable for blocks of BEGIN TRANSACTION followed by COMMIT or ROLLBACK to be nested within other such blocks. You can also do this kind of nesting with calls to nested stored procedures. However, the semantics of such a formulation might not be what you'd expect if you think that the transactions are truly nested: they aren't. But the behavior is reasonable and predictable. A ROLLBACK rolls back all levels of the transaction, not only its inner block. A COMMIT TRAN does nothing to commit the transaction if the statement isn't part of the outermost block. If the COMMIT TRAN is part of the outermost block, it commits all levels of the transaction.
So the behavior of a COMMIT or a ROLLBACK isn't too orthogonal when the transaction blocks are nested. Only the outermost COMMIT is able to commit the transaction, but any ROLLBACK will roll back the entire transaction at all levels. If this weren't true, a ROLLBACK in an outer transaction wouldn't be able to perform its job, because the data would have already been committed. This behavior allows stored procedures (and triggers) to be executed automatically and in a predictable way without your needing to check the transaction state. A nested stored procedure that does a ROLLBACK will roll back the entire transaction, including work done by the top-level procedure.
In addition, the blocks of BEGIN TRAN and COMMIT or ROLLBACK are determined only by what actually executes, not by what's present in the batch. If conditional branching occurs (via IF statements, for example) and one of the statements doesn't execute, the statement isn't part of a block.
A common misconception about ROLLBACK is that it changes the flow of control, causing , for example, an immediate return from a stored procedure or a batch. However, flow of control continues to the next statement, which, of course, could be an explicit RETURN. ROLLBACK affects only the actual data; it doesn't affect local variables or SET statements. If local variables are changed during a transaction, or if SET statements are issued, those variables and options do not revert to the values they had before the transaction started. Variables and SET options aren't part of transaction control.
The system function @@TRANCOUNT will return the depth of executed BEGIN TRAN blocks. You can think of the behavior of COMMIT and ROLLBACK in this way: ROLLBACK performs its job for all levels of transaction blocks whenever @@TRANCOUNT is 1 or greater. A COMMIT commits changes only when @@TRANCOUNT is 1.
Below, you'll see several examples of nesting transaction blocks. For illustration, the value of @@TRANCOUNT is shown in the comments. If this discussion isn't totally clear to you, you should work through these examples by hand to understand why the value of @@TRANCOUNT is what it is, why it changes, and why the behavior of COMMIT or ROLLBACK acts in a way that depends on the @@TRANCOUNT value.
Executing a BEGIN TRAN statement always increments the value of @@TRANCOUNT. If no transaction is active, @@TRANCOUNT is 0. Executing a COMMIT TRAN decrements the value of @@TRANCOUNT. Exe- cuting a ROLLBACK TRAN rolls back the entire transaction and sets @@TRANCOUNT to 0. Executing either a COMMIT or a ROLLBACK when there's no open transaction (@@trancount is 0) results in error 3902 or 3903, which states that the COMMIT or ROLLBACK request has no corresponding BEGIN TRANSACTION. In this case, @@trancount is not decremented, so it can never go below 0.
Other errors, not of your making, are fatal as well ”for example, running out of memory, filling up the transaction log (if you don't have it set to autogrow), or terminating because of a deadlock. Such errors cause an open transaction to automatically roll back. If that occurs, @@trancount will return 0, signaling that no open transaction exists.
The following incidents cause fatal errors:
You can't plan precisely for all of these situations, and even if you could, a new release could likely add another one. In a production environment, fatal errors should be few and far between. But as is true in nearly all programming, it's up to you to decide whether you want to try to plan for every conceivable problem that could happen and then deal with each specifically , or whether you'll accept the system default behavior. In SQL Server, the default behavior would typically be to raise an error for the condition encountered and then, when the COMMIT or ROLLBACK is executed, raise another that says it has no corresponding transaction.
One possibility is to write some retry logic in your client application to deal with a possible deadlock condition. For the other error conditions, you might decide to go with the default error behavior. If your applications are deployed with proper system management, such errors should be nonexistent or rare. You could also check @@TRANCOUNT easily enough before executing a ROLLBACK or COMMIT to ensure that a transaction to operate on is open.
The nesting of transaction blocks provides a good reason for you not to name the transaction in a ROLLBACK statement. If, in a rollback, any transaction other than the top-level transaction is named, error 6401 will result:
Cannot rollback XXX - no transaction or savepoint of that name found.
It's fine to name the transaction in the BEGIN TRAN block, however. A COMMIT can also be named, and it won't prompt an error if it's not paired in the top-level branch, because it's basically a NO-OP in that case anyway (except that it decrements the value returned by @@trancount).
One reason for naming your BEGIN TRAN statements is to allow your COMMIT and ROLLBACK statements to be self-documenting . ROLLBACK TRAN doesn't require a transaction name, but you can include one as a way of emphasizing that you are rolling back the entire transaction. The following batch will result in an error. The statement ROLLBACK TRAN B will fail, with error 6401. The subsequent ROLLBACK TRAN A will succeed because it's the top-level transaction block. Remember, though, that we'd get the same behavior if we simply used ROLLBACK TRAN instead of ROLLBACK TRAN A.
-- To start with, verify @@TRANCOUNT is 0 SELECT @@TRANCOUNT BEGIN TRAN A -- Verify @@TRANCOUNT is 1 SELECT @@TRANCOUNT -- Assume some real work happens here BEGIN TRAN B -- Verify @@TRANCOUNT is 2 SELECT @@TRANCOUNT -- Assume some real work happens here ROLLBACK TRAN B -- @@TRANCOUNT is still 2 because the previous ROLLBACK -- failed due to error 6401 SELECT @@TRANCOUNT -- Assume some real work happens here ROLLBACK TRAN A -- This ROLLBACK succeeds, so @@TRANCOUNT is back to 0 SELECT @@TRANCOUNT
The following example is arguably an improvement over the previous attempt. The first ROLLBACK will execute and the second ROLLBACK will fail, with error 3903:
Server: Msg 3903, Level 16, State 1 The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.
The first ROLLBACK did its job, and there is no open transaction:
-- To start with, verify @@TRANCOUNT is 0 SELECT @@TRANCOUNT BEGIN TRAN A -- Verify @@TRANCOUNT is 1 SELECT @@TRANCOUNT -- Assume some real work happens here BEGIN TRAN B -- Verify @@TRANCOUNT is 2 SELECT @@TRANCOUNT -- Assume some real work happens here ROLLBACK TRAN -- Notice the tran is unnamed but works -- That ROLLBACK terminates transaction. @@TRANCOUNT is now 0. SELECT @@TRANCOUNT -- The following ROLLBACK will fail because there is no open -- transaction (that is, @@TRANCOUNT is 0) ROLLBACK TRAN -- @@TRANCOUNT does not go negative. It remains at 0. SELECT @@TRANCOUNT
If you syntactically nest transactions, be careful not to nest multiple ROLLBACK statements in a way that would allow more than one to execute. To illustrate that COMMIT behaves differently than ROLLBACK, note that the following example will run without error. However, the statement COMMIT TRAN B doesn't commit any changes. It does have the effect of decrementing @@trancount, however.
-- To start with, verify @@TRANCOUNT is 0 SELECT @@TRANCOUNT BEGIN TRAN A -- Verify @@TRANCOUNT is 1 SELECT @@TRANCOUNT -- Assume some real work happens here BEGIN TRAN B -- Verify @@TRANCOUNT is 2 SELECT @@TRANCOUNT -- Assume some real work happens here COMMIT TRAN B -- The COMMIT didn't COMMIT anything, but does decrement -- @@TRANCOUNT -- Verify @@TRANCOUNT is back down to 1 SELECT @@TRANCOUNT -- Assume some real work happens here COMMIT TRAN A -- The COMMIT on previous line does commit the changes and -- closes the transaction. -- Since there's no open transaction, @@TRANCOUNT is again 0. SELECT @@TRANCOUNT
In summary, nesting transaction blocks is perfectly valid, but you must understand the semantics. If you understand when @@trancount is incremented, decremented, and set to 0, and you know the simple rules for COMMIT and ROLLBACK, you can pretty easily produce the effect you want.
Often, users will nest transaction blocks, only to find that the behavior isn't what they want. What they really want is for a savepoint to occur in a transaction. A savepoint provides a point up to which a transaction can be undone ”it might have been more accurately named a "rollback point." A savepoint doesn't commit any changes to the database ”only a COMMIT statement can do that.
SQL Server allows you to use savepoints via the SAVE TRAN statement, which doesn't affect the @@TRANCOUNT value. A rollback to a savepoint (not a transaction) doesn't affect the value returned by @@TRANCOUNT, either. However, the rollback must explicitly name the savepoint: using ROLLBACK without a specific name will always roll back the entire transaction.
In the first nested transaction block example shown above, a rollback to a transaction name failed with error 6401 because the name wasn't the top-level transaction. Had the name been a savepoint instead of a transaction, no error would've resulted, as this example shows:
-- To start with, verify @@TRANCOUNT is 0 SELECT @@TRANCOUNT BEGIN TRAN A -- Verify @@TRANCOUNT is 1 SELECT @@trancount -- Assume some real work happens here SAVE TRAN B -- Verify @@TRANCOUNT is still 1. A savepoint does not affect it. SELECT @@TRANCOUNT -- Assume some real work happens here ROLLBACK TRAN B -- @@TRANCOUNT is still 1 because the previous ROLLBACK -- affects just the savepoint, not the transaction SELECT @@TRANCOUNT -- Assume some real work happens here ROLLBACK TRAN A -- This ROLLBACK succeeds, so @@TRANCOUNT is back to 0 SELECT @@TRANCOUNT
Stored procedures take parameters, and you can give parameters default values. If you don't supply a default value when creating the procedure, an actual parameter will be required when executing the procedure. If you don't pass a required parameter, an error like this will result:
Msg 201, Level 16, State 2 Procedure sp_passit expects parameter @param1, which was not supplied.
You can pass values by explicitly naming the parameters or by furnishing all the parameter values anonymously but in correct positional order. You can also use the keyword DEFAULT as a placeholder in passing parameters. NULL can also be passed as a parameter (or defined to be the default). Here's a simple example with results in bold:
CREATE PROCEDURE pass_params @param0 int=NULL, -- Defaults to NULL @param1 int=1, -- Defaults to 1 @param2 int=2 -- Defaults to 2 AS SELECT @param0, @param1, @param2 GO EXEC pass_params -- PASS NOTHING - ALL Defaults (null) 1 2 EXEC pass_params 0, 10, 20 -- PASS ALL, IN ORDER 0 10 20 EXEC pass_params @param2=200, @param1=NULL -- Explicitly identify last two params (out of order) (null) (null) 200 EXEC pass_params 0, DEFAULT, 20 -- Let param1 default. Others by place. 0 1 20