Setting Return Status

You can set the return status of a procedure with the RETURN statement. Look at this enhanced version of the price lookup procedure we wrote earlier. It sets a non-zero return status if the item requested is not found, or if it doesn’t have a non-null price:

drop proc pr_itemprice_lookup
go
create proc pr_itemprice_lookup
(@item_id int, @price money output)
as
select @price = price
from   items
where item_id = @item_id
if @@rowcount = 0
   return 75        -- row not found
if @price is null
   return 76        -- price is null
return 0            -- normal return - price is ok

I’ve used the values 75 and 76 to mean row not found and price is null, respectively. Why 75 and 76? I made them up. I could have used any non-zero number outside of the reserved range (-99 to -1). The important thing is to establish standard definitions for return status and to use those values as widely as possible in your organization.

We’ll have to rewrite the order insert procedure to capture those values and also to do some error trapping of its own:

drop proc pr_insertorder
go
create proc pr_insertorder
(@item_id int,
 @qty int,
 @order_id int output)
as
declare @priceval money, @retstatus int
execute @retstatus = pr_itemprice_lookup @item_id, @priceval output
if @retstatus != 0
begin
   if @retstatus = 75
      raiserror (“invalid item id: please try again”, 16, 1)
   else
      if @retstatus = 76
         raiserror (“there is no price for item id %d”, 16, 1, @item_id)
      else
         raiserror (“General failure in procedure pr_itemprice_lookup”,
16, 1)
   return 99
end
insert orders (item_id, qty, unit_price, ext_price)
values (@item_id, @qty, @priceval, @qty*@priceval)
if @@error != 0
   return 77
select @order_id = @@identity
return 0

---

Note:  By now, you probably realized that writing stored procs is real programming. Until now, 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.

---

We should look at some of the new aspects of the procedure:

declare @priceval money, @retstatus int
execute @retstatus = pr_itemprice_lookup @item_id, @priceval output

The procedure retrieves a return status after executing the inner procedure, pr_itemprice_lookup.

if @retstatus != 0
begin
   if @retstatus = 75
      raiserror (“invalid item id: please try again”, 16, 1)
   else
      if @retstatus = 76
         raiserror (“there is no price for item id %d”, 16, 1, @item_id)
      else
         raiserror (“General failure in procedure pr_itemprice_lookup”,
16, 1)
   return 99
end

The error check looks for three conditions: 75, 76, and any other non-zero return status. If you look at pr_itemprice_lookup, the procedure itself returns only with the values 75, 76, and 0. So here’s the question: What other non-zero return values could this procedure return? The answer is, any of the SQL Server returns (–1 to –99) listed earlier. When you execute a stored procedure from another program, always retrieve and examine the return status, regardless of whether return status is implemented by the procedure programmer.

insert orders (item_id, qty, unit_price, ext_price)
values (@item_id, @qty, @priceval, @qty*@priceval)
if @@error != 0
     return 77

This error check tests the insert to make certain it succeeded. Any non-zero value for @@error should cause the procedure to exit and return a non-zero status.

Notice also that the procedure exits before a value is assigned to @order_id. This is important because the value of @@identity is set the first time (within a SQL Server session) that you insert a row into a table containing an identity column. This value is not automatically nulled or zeroed by a subsequent failed attempt to insert a row. You need to make certain that you have assigned a proper value (often null) to output parameters before exiting in an error case.