SQL Return

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.

SYNTAX

RETURN [ integer_expression ]

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.

Also Read..

SQL WAITFOR

Try..Catch

While Loop

IF..ELSE

GOTO Statement