Sybase Technical Library - Product Manuals Home
[Search Forms] [Previous Section with Hits] [Next Section with Hits] [Clear Search] Expand Search

Returning information from stored procedures [Table of Contents] Checking roles in procedures

Transact-SQL User's Guide

[-] Chapter 14 Using Stored Procedures
[-] Returning information from stored procedures
[-] Return status

Return status

Stored procedures report a return status that indicates whether or not they completed successfully, and if they did not, the reasons for failure. This value can be stored in a variable when a procedure is called, and used in future Transact-SQL statements. Adaptive Server-defined return status values for failure range from -1 through -99; users can define their own return status values outside this range.

Here is an example of a batch that uses the form of the execute statement that returns the status:

declare @status int 
execute @status = byroyalty 50
select @status

The execution status of the byroyalty procedure is stored in the variable @status. "50" is the supplied parameter, based on the royaltyper column of the titleauthor table. This example prints the value with a select statement; later examples use this return value in conditional clauses.

Reserved return status values

Adaptive Server reserves 0, to indicate a successful return, and negative values from -1 through -99, to indicate the reasons for failure. Numbers 0 and -1 through -14 are currently used in version 12, as shown in Table 14-1:

Reserved return status values




Procedure executed without error


Missing object


Datatype error


Process was chosen as deadlock victim


Permission error


Syntax error


Miscellaneous user error


Resource error, such as out of space


Non-fatal internal problem


System limit was reached


Fatal internal inconsistency


Fatal internal inconsistency


Table or index is corrupt


Database is corrupt


Hardware error

Values -15 through -99 are reserved for future use by Adaptive Server.

If more than one error occurs during execution, the status with the highest absolute value is returned.

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 
if (select contract from titles where 
        title_id = @titleid) = 1 
   return 1 
   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 
declare @retvalue int 
execute @retvalue = checkcontract @titleid  
if (@retvalue = 1) 
   print "Contract is valid." 
    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 

Returning information from stored procedures [Table of Contents] Checking roles in procedures