User-Generated Return Values

You can generate your own return values in stored procedures by adding a parameter to the return statement. You can use any integer outside the 0 through -99 range.

The following example returns 1 when a book has a valid contract and returns 2 in all other cases:

create proc checkcontract @titleid tid 
as 
if (select contract from titles where 
        title_id = @titleid) = 1 
   return 1 
else 
   return 2

For example:

checkcontract MC2222

(return status = 1)

The following stored procedure calls checkcontract, and uses conditional clauses to check the return status:

create proc get_au_stat @titleid tid 
as 
declare @retvalue int 
execute @retvalue = checkcontract @titleid  
if (@retvalue = 1) 
   print "Contract is valid." 
else 
    print "There is not a valid contract."

Here are the results when you execute get_au_stat with the title_id of a book with a valid contract:

get_au_stat MC2222

Contract is valid