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
