Returning Procedure Status

So far we have been writing very optimistic code: only a little error checking and very few validations. Unfortunately, there's plenty that could go wrong here.

Let's look closely at the prAddItemToCart procedure we just finished writing. If something goes wrong, we shouldn't insert an order, and the user or her application should be warned. What if we couldn't access the default unit price because of database problems, network problems, and so forth? You can't insert an order without a price. All these circumstances are handled with the procedure return status.

The procedure return status describes the state of the procedure when it ended. It tells the calling program why the procedure stopped executing.

Under normal circumstances, a procedure ends its execution because it reaches the end of the procedure, or the procedure executes a RETURN statement. This normal end of a procedure has a return status of 0 by default. Almost all SQL Server application developers agree to use the value of 0 to mean that the procedure executed successfully.

To retrieve the return status from a procedure, declare a variable and use it in the EXECUTE statement. Then report the value of the return status in the next step. For example, if I try to enter another item to this order for ProductID 5 using prAddItemToCart, the insert will fail. (There's a primary key constraint on OrderID and ProductID.) This can be seen in the Listing 15.5.

Code Listing 15.5. Retrieving a Return Status

1: declare @retstatus int
2: exec @retstatus = prAddItemToCart 11078, 5
3: print 'Return status is '+ cast(@retstatus as nchar(10))

Results:

Server: Msg 2627, Level 14, State 1,
Procedure prAddItemToCart, Line 25
Violation of PRIMARY KEY constraint 'PK_Order_Details'.
Cannot insert duplicate key in object 'Order Details'.
The statement has been terminated.
Return status is 99
					

The server returned an error alerting me to the primary key violation. The INSERT statement failed, and the return status is 99. What if we had not inserted an error check (lines 39 through 40 in Listing 15.4, if @@error != 0 …) after the insert statement? The error would have been raised, but the program calling the procedure might not have been aware that the procedure call had failed.

By now, you probably realized that writing stored procedures is real programming. Up to this point, it wasn't all that bad, but now we have real error checking, and the code is getting longer.

The fact is, a stored procedure is seldom longer than two or three pages, and almost never longer than ten. If you find yourself writing a ten-page procedure, you probably made a mistake in the design that needs to be corrected. SQL is such a compact language that there is seldom need to write a whole lot of it, even with solid error checking and plenty of commentary.