SQL Return is a control-of-flow language keyword which is used to exit unconditionally from a query or stored procedure. It can be used at any point to exit from a stored procedure, batch, or statement block.
RETURN [ integer_expression ]
Is the integer value that is returned.
When RETURN statement is executed that it returns an integer value. If the return value is not provided, the default is 0.
Returning a status code from stored procedure
Lets create a stored procedure that will return status code based on certain condition check.
First we will create a sample table named tblcode then insert some dummy records in this table.
Create table dbo.tblcode ( CodeId Int, Code Varchar(10) ) Insert into tblcode (CodeId, Code) Values (1, 'XCV246'), (2, 'CBD43'), (3, 'VSD35'), (4, 'GSD22')
You can see table is created, it have four records as shown below.
Select * from dbo.tblcode
Lets create a stored procedure which will take a code as an input value and check the existence of that code in table, If code does exist then it will return value as 1 else 0.
Lets create stored procedure named IsCodeExists as shown below.
Create proc dbo.IsCodeExists ( @code varchar(10) ) as begin declare @statuscode int; if exists( select 1 from dbo.tblcode where code = @code ) begin set @statuscode= 1; end else begin set @statuscode= 0; end return @statuscode; end
Lets execute the stored procedure to check whether code ‘XCV246’ exists or not.
To execute the stored procedure which uses return statement with return value you need to declare a local variable to store the returned value from stored procedure before executing the stored procedure as shown below.
DECLARE @status int EXEC @status = IsCodeExists 'XCV246' SELECT @status as status;
The @status is a local variable which store the value, returned of stored procedure.
You can see the output of stored procedure, it returns status 1 that means code exists in database table.
Returning from statement block
Lets see how to use Return statement to immediate exit from while loop when loop count reaches to 5.
Here we have a while loop block which returns number starting from 1 to 10.
Declare @i int; Set @i = 1 while (@i < =10) begin print @i set @i =@i+1 end
You can see the output, it prints numbers from 1 to 10.
Lets use the RETURN that causes the control flow to exit from looping when loop count reaches to 5.
Lets modify the above code block as shown below.
Declare @i int; set @i = 1 while (@i <= 10) begin print @i if(@i = 5) return set @i = @i+1 end
You can see when loop counts reaches to 5, it immediately terminate a control flow of while loop block.