RETURN (Transact-SQL)
Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance
Exits unconditionally from a query or procedure. RETURN is immediate and complete and can be used at any point to exit from a procedure, batch, or statement block. Statements that follow RETURN are not executed.
Transact-SQL syntax conventions
Syntax
RETURN [ integer_expression ]
Arguments
integer_expression
Is the integer value that is returned. Stored procedures can return an integer value to a calling procedure or an application.
Note
To view Transact-SQL syntax for SQL Server 2014 (12.x) and earlier versions, see Previous versions documentation.
Return Types
Optionally returns int.
Note
Unless documented otherwise, all system stored procedures return a value of 0. This indicates success and a nonzero value indicates failure.
Remarks
When used with a stored procedure, RETURN cannot return a null value. If a procedure tries to return a null value (for example, using RETURN @status when @status is NULL), a warning message is generated and a value of 0 is returned.
The return status value can be included in subsequent Transact-SQL statements in the batch or procedure that executed the current procedure, but it must be entered in the following form: EXECUTE @return_status = <procedure_name>
.
Examples
A. Returning from a procedure
The following example shows if no user name is specified as a parameter when findjobs
is executed, RETURN
causes the procedure to exit after a message has been sent to the user's screen. If a user name is specified, the names of all objects created by this user in the current database are retrieved from the appropriate system tables.
CREATE PROCEDURE findjobs @nm sysname = NULL
AS
IF @nm IS NULL
BEGIN
PRINT 'You must give a user name'
RETURN
END
ELSE
BEGIN
SELECT o.name, o.id, o.uid
FROM sysobjects o INNER JOIN master..syslogins l
ON o.uid = l.sid
WHERE l.name = @nm
END;
B. Returning status codes
The following example checks the state for the ID of a specified contact. If the state is Washington (WA
), a status of 1
is returned. Otherwise, 2
is returned for any other condition (a value other than WA
for StateProvince
or ContactID
that did not match a row).
USE AdventureWorks2022;
GO
CREATE PROCEDURE checkstate @param VARCHAR(11)
AS
IF (SELECT StateProvince FROM Person.vAdditionalContactInfo WHERE ContactID = @param) = 'WA'
RETURN 1
ELSE
RETURN 2;
GO
The following examples show the return status from executing checkstate
. The first shows a contact in Washington; the second, contact not in Washington; and the third, a contact that is not valid. The @return_status
local variable must be declared before it can be used.
DECLARE @return_status INT;
EXEC @return_status = checkstate '2';
SELECT 'Return Status' = @return_status;
GO
Here is the result set.
Return Status
-------------
1
Execute the query again, specifying a different contact number.
DECLARE @return_status INT;
EXEC @return_status = checkstate '6';
SELECT 'Return Status' = @return_status;
GO
Here is the result set.
Return Status
-------------
2
Execute the query again, specifying another contact number.
DECLARE @return_status INT
EXEC @return_status = checkstate '12345678901';
SELECT 'Return Status' = @return_status;
GO
Here is the result set.
Return Status
-------------
2
See Also
ALTER PROCEDURE (Transact-SQL)
CREATE PROCEDURE (Transact-SQL)
DECLARE @local_variable (Transact-SQL)
EXECUTE (Transact-SQL)
SET @local_variable (Transact-SQL)
THROW (Transact-SQL)
प्रतिक्रिया
https://aka.ms/ContentUserFeedback.
जल्द आ रहा है: 2024 के दौरान हम सामग्री के लिए फीडबैक तंत्र के रूप में GitHub मुद्दों को चरणबद्ध तरीके से समाप्त कर देंगे और इसे एक नई फीडबैक प्रणाली से बदल देंगे. अधिक जानकारी के लिए, देखें:के लिए प्रतिक्रिया सबमिट करें और देखें