Exits unconditionally from a query or procedure. RETURN is immediate and complete; statements following RETURN are not executed.
RETURN ([integer_expression])
where
The RETURN keyword can be used at any point where you want to exit from a procedure, batch, or statement block.
When used with a stored procedure, RETURN cannot return a null value. 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 through - 14 is returned.
SQL Server reserves 0 to indicate a successful return and reserves negative values from - 1 through - 99 to indicate different reasons for failure. If no user-defined return value is provided, the SQL Server value is used. User-defined return status values should not conflict with those reserved by SQL Server. The values 0 through -14 are currently in use.
| Value | Meaning | |
|---|---|---|
| 0 | Procedure was executed successfully. | |
| -1 | Object is missing. | |
| -2 | Datatype error occurred. | |
| -3 | Process was chosen as deadlock victim. | |
| -4 | Permission error occurred. | |
| -5 | Syntax error occurred. | |
| -6 | Miscellaneous user error occurred. | |
| -7 | Resource error, such as out of space, occurred. | |
| -8 | Nonfatal internal problem was encountered. | |
| -9 | System limit was reached. | |
| -10 | Fatal internal inconsistency occurred. | |
| -11 | Fatal internal inconsistency occurred. | |
| -12 | Table or index is corrupt. | |
| -13 | Database is corrupt. | |
| -14 | Hardware error occurred. | |
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 those supplied by SQL Server.
You can include the return status value in subsequent Transact-SQL statements in the batch or procedure that executed the current procedure, but you must enter it in the following form:
EXECute @return_status = procedure_name
For more information, see the EXECUTE statement.