SQL Server Stored Procedure Return Value

Back to: SQL Server Tutorial For Beginners and Professionals

SQL Server Stored Procedure Return Value With Examples

In this article, I am going to discuss the SQL Server Stored Procedure Return Value with examples. Please read our previous article before proceeding to this article where we discussed the Output Parameters in SQL Server Stored Procedure with examples. As part of this article, we are going to discuss the following pointers.

1. What is Return Value in Stored Procedure?
2. Multiple Examples to understand the Return Status Variable in SQL Server Stored Procedure.
3. Disadvantages of Return Status Value in SQL Server?
4. What are the differences between Return Status Value and Output Parameters in SQL Server?

What is Return Value in SQL Server Stored Procedure?

Whenever we execute a stored procedure in SQL Server, it always returns an integer status variable indicating the status, usually, zero indicates success, and non-zero indicates the failure. To see this yourself, execute any stored procedure from the object explorer, in SQL server management studio.

1. Right Click and select Execute Stored Procedure
2. If the procedure, expects parameters, provide the values and click OK
3. Along with the result that you expect, the stored procedure also returns a Return Value = 0

So, from this point, we understood that, when a stored procedure is executed, it returns an integer status variable. With this in mind, let’s understand the difference between the output parameters and SQL Server Stored Procedure Return Values.

We are going to use the following Employee table to understand the Stored Procedure Output Parameters and Return values in SQL Server.

Please use the below SQL Script to create and populate the Employee table with some test data.

-- Create Employee Table
CREATE TABLE Employee
(
  ID INT PRIMARY KEY,
  Name VARCHAR(50),
  Gender VARCHAR(50),
  DOB DATETIME,
  DeptID INT
)
GO

-- Populate the Employee Table with test data
INSERT INTO Employee VALUES(1, 'Pranaya', 'Male','1996-02-29 10:53:27.060', 1)
INSERT INTO Employee VALUES(2, 'Priyanka', 'Female','1995-05-25 10:53:27.060', 2)
INSERT INTO Employee VALUES(3, 'Anurag', 'Male','1995-04-19 10:53:27.060', 2)
INSERT INTO Employee VALUES(4, 'Preety', 'Female','1996-03-17 10:53:27.060', 3)
INSERT INTO Employee VALUES(5, 'Sambit', 'Male','1997-01-15 10:53:27.060', 1)
INSERT INTO Employee VALUES(6, 'Hina', 'Female','1995-07-12 10:53:27.060', 2)
GO

Example1:

Create a procedure that will count the total number of employees in the Employee table using the output parameter.

CREATE PROCEDURE spGetTotalCountOfEmployee1
  @TotalCount INT OUTPUT
AS
BEGIN
  SELECT @TotalCount =COUNT(ID)FROM Employee
END

-- For calling the procedure:
DECLARE @EmployeeTotal INT
EXECUTE spGetTotalCountOfEmployee1 @EmployeeTotal OUTPUT
PRINT @EmployeeTotal

When we execute the above SP it returns 6.

Note: While calling a procedure with output parameters we need to declare variables first and substitute them in the place of the parameter list so that the results come and sit in those variables but here also we need to use OUT/OUTPUT keywords.

Example2:

Create a procedure that will count the total number of employees in the Employee table using return status.

CREATE PROCEDURE spGetTotalCountOfEmployee2
AS
BEGIN
  RETURN (SELECT COUNT(ID) FROM Employee)
END

-- For calling the procedure:
DECLARE @EmployeeTotal INT
EXECUTE @EmployeeTotal = spGetTotalCountOfEmployee2
PRINT @EmployeeTotal

When we execute the above SP, it also returns 6.

So we are able to achieve what we want using both output parameters as well as return values. Now let’s look at an example where the return status variable cannot be used but the output parameter can be used.

Example3:

Create a procedure that will take the id value of an employee and return the name of that employee using the output parameter.

CREATE PROCEDURE spGetEmplloyeeNameById1
  @ID INT,
  @Name VARCHAR(30) OUTPUT
AS
BEGIN
  SELECT @Name = Name FROM Employee WHERE ID = @ID
END
GO

-- For calling the procedure:
DECLARE @EmployeeName VARCHAR(30)
EXECUTE spGetEmplloyeeNameById1 3, @EmployeeName OUTPUT
PRINT @EmployeeName

When we execute the above query, it will print the name of the employee as expected.

Now let’s achieve the same thing using return status value.

CREATE PROCEDURE spGetEmplloyeeNameById2
  @ID INT
AS
BEGIN
  RETURN (SELECT Name FROM Employee WHERE ID = @ID)
END
GO

-- For calling the procedure:
DECLARE @EmployeeName VARCHAR(30)
EXECUTE @EmployeeName = spGetEmplloyeeNameById2 3
PRINT @EmployeeName

When we execute the spGetEmplloyeeNameById2 Stored Procedure it returns an error stating ‘Conversion failed when converting the nvarchar value Anurag to data type int.‘. The return status variable is an integer, and hence when we select the Name of an employee and try to return that we get a conversion error. 

So, in SQL Server by using the return values, we can return only one integer. It is not possible, to return more than one value using return values, whereas in output parameters, we can return any data type and a stored procedure can have more than one output parameter.

In general, Return value is used to indicate the success or failure of the stored procedure, especially when we are dealing with nested stored procedures. A return value of 0, indicates success, and any nonzero value indicates failure.

What are the Disadvantages of Return Status Value in SQL Server?

Following are the things that we can’t achieve using the Return Value in SQL Server.

1. We cannot return more than one value.
2. We cannot return values other than an integer.

But these two can possible with output parameters.

What are the differences between Return Status Value and Output Parameters in SQL Server Stored Procedure?

Return Status Variable	Output parameters
Only integer data type it can return	Any data type value it can return
Only one value	More than one value
Use to indicate success or failure	Use to return values like name, age, salary, count, etc.

In the next article, I am going to discuss the Temporary Stored Procedure in SQL Server with examples. Here, in this article, I try to explain the Return Value in SQL Server Stored Procedure with some examples. I hope you enjoy this article.