SQL Server Stored Procedure Return Values With Examples
In this article, I am going to discuss the SQL Server Stored Procedure Return Values. Then we will discuss the differences between Output Parameters and Return Status values in SQL Server Stored Procedure with examples. Please read the following three articles before proceeding to this article.
What are SQL Server Stored Procedure Return Values?
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.
- Right Click and select Execute Stored Procedure
- If the procedure, expects parameters, provide the values and click OK
- 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 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
Create a procedure that will count the total number of employee 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 in the place of parameter list so that the results come and sit in those variables but here also we need to use OUT/OUTPUT keywords.
Create a procedure that will count the total number of employee 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 return status variable cannot be used but output parameter can be used.
Create a procedure which 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 SP 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 output parameters, we can return any datatype and a stored procedure can have more than one output parameters.
In general, RETURN values are 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.
Explain the Disadvantages of Return status value.
- We cannot return more than one value.
- We cannot return values other than an integer.
But this two can possible with output parameters.
What are the differences between return status value and output parameters?
|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 will discuss the Temporary Stored Procedure in SQL Server with examples.
In this article, I try to explain the SQL Server Stored Procedure Return Values step by step with some examples. I hope this article will help you with your need. I would like to have your feedback. Please post your feedback, question, or comments about this article.