Exits from a batch or procedure unconditionally and provides an optional return status. Statements following return are not executed.
return [integer_expression] [plan "abstract_plan"]
create procedure findrules @nm varchar (30) = null as if @nm is null begin print "You must give a user name" return end else begin select sysobjects.name, sysobjects.id, sysobjects.uid from sysobjects, master..syslogins where master..syslogins.name = @nm and sysobjects.uid = master..syslogins.suid and sysobjects.type = "R" end
print "Begin update batch" update titles set price = price + $3 where title_id = 'BU2075' update titles set price = price + $3 where title_id = 'BU1111' if (select avg (price) from titles where title_id like 'BU%') > $15 begin print "Batch stopped; average price over $15" return end update titles set price = price + $2 where title_id = 'BU1032'
create proc checkcontract @param varchar (11) as declare @status int if (select contract from titles where title_id = @param) = 1 return 1 else return 2
execute @retval = procedure_name
The SAP ASE server reserves 0 to indicate a successful return, and negative values in the range -1 to -99 to indicate different reasons for failure. If no user-defined return value is provided, the SAP ASE value is used. User-defined return status values cannot conflict with those reserved by the SAP ASE server. Numbers 0 and -1 through -14 are currently in use:
Value |
Meaning |
---|---|
0 |
Procedure executed without error |
-1 |
Missing object |
-2 |
Datatype error |
-3 |
Process was chosen as deadlock victim |
-4 |
Permission error |
-5 |
Syntax error |
-6 |
Miscellaneous user error |
-7 |
Resource error, such as out of space |
-8 |
Nonfatal internal problem |
-9 |
System limit was reached |
-10 |
Fatal internal inconsistency |
-11 |
Fatal internal inconsistency |
-12 |
Table or index is corrupt |
-13 |
Database is corrupt |
-14 |
Hardware error |
Values -15 to -99 are reserved for future SAP ASE use.
If more than one error occurs during execution, the status with the highest absolute value is returned. User-defined return values always take precedence over SAP ASE-supplied return values.
The return command can be used at any point where you want to exit from a batch or procedure. Return is immediate and complete: statements after return are not executed.
A stored procedure cannot return a NULL return status. If a procedure attempts to return a null value, for example, using return @status where @status is NULL, a warning message is generated, and a value in the range of 0 to -14 is returned.
ANSI SQL – Compliance level: Transact-SQL extension.
No permission is required to use return.