Return Values in SQL Stored Procedure

How to use Return Values in SQL Stored Procedure or How to use Return Values to return a message from a Stored Procedure with an example.

Return Values in a Stored Procedure in Sql Server will return integer values only. By default, it returns 0, if you execute any stored procedure successfully. For this stored procedure return output demonstration, We are going to use the below-shown table

Return Values in SQL Stored Procedure Example

In this example, we will show you the default return value returned by the Server. To demonstrate the same, let me execute the previously created Stored Procedure.

I suggest you refer Introduction to SP article to learn the basics of the SQL Server stored procedure. And refer to Input Parameters in a SP article to know the definition behind this stored procedure.

Here, we are assigning the Occupation value as Management.

Once you click on the OK button, a new query window will open with the following auto-generated code. As you can see, it is returning 0 as a return value. 0 means successful.

DECLARE	@return_value int

EXEC	@return_value = [dbo].[spGetEmployeeDetails]
		@Occupation = N'Management'

SELECT	'Return Value' = @return_value

GO

Return Values in Stored Procedure Example 2

In this return value example, we will show how to use return values in Stored procedures. Please refer to the Select SP article to write the Select statement inside a stored procedure.

Within the procedure, we are using the COUNT to find the number of employees in the Employee Duplicate table, and then we return the value.

-- Example for Stored Procedure with Output Parameters

CREATE PROCEDURE [dbo].[spCountEmployees]
AS
BEGIN
	RETURN (SELECT COUNT(EmpID)FROM [EmployeeDuplicates])
END

Messages
--------
Command(s) completed successfully.

Let me execute the stored procedure.

-- Declare a Variable of Type Int. Should match with SP's Output parameter
DECLARE	@TotalEmployees INT 

-- Assigning the Output of a Stored Procedure to Declared variable
EXEC @TotalEmployees = [dbo].[spCountEmployees]

-- Printing the Output
PRINT @TotalEmployees

SP return values Example 3

In this stored procedure example, Let me show you what will happen If we print a String (Varchar) data as the return value. Here, we are selecting the Occupation of an employee whose last name is equal to Gateway. (Instead of hard coding a Value, you can also try Input Parameter).

CREATE PROCEDURE [dbo].[spEmployeeName]
AS
BEGIN
 RETURN (SELECT [Occupation] FROM [EmployeeDuplicates]
         WHERE [LastName] = N'Gateway')
END

Messages
--------
Command(s) completed successfully.

Let me execute the stored procedure.

DECLARE	@EmpName VARCHAR(50) 
EXEC @EmpName = [dbo].[spEmployeeName]
PRINT @EmpName

As you can see from the below screenshot that it is throwing an error. because Return Values always display integers only.