In this article we will show you, How to use Return Values in SQL Stored Procedure , Or How to use Return Values to return a message from a SQL Stored Procedure with example. I suggest you to refer Introduction to Stored Procedure article to understand the basics of the stored procedure.
Return Values in a Stored Procedure will return integer values only. By default it returns 0, if you execute any stored procedure successfully. For this demonstration, We are going to use the below shown SQL table
Return Values in SQL Stored Procedure Example 1
In this example we will show you, the default return value returned by the SQL Server. To demonstrate the same, let me execute the previously created Stored Procedure. Please refer Input Parameters in a Stored procedure article to understand 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 be opened with the following auto generated code. As you can see, it is returning 0 as return value. 0 means successful.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
-- Example for SQL Stored Procedure with Output Parameters USE [SQL Tutorial] GO DECLARE @return_value int EXEC @return_value = [dbo].[spGetEmployeeDetails] @Occupation = N'Management' SELECT 'Return Value' = @return_value GO |
OUTPUT
Return Values in SQL Stored Procedure Example 2
In this example we will show you, How to use return values in a Stored procedures. Please refer Select Stored Procedure article to understand, How to write Select statement inside a stored procedure.
Within the procedure, we are using the COUNT to find the number of employees in Employee Duplicate table, and then we are return the value.
|
1 2 3 4 5 6 7 8 9 |
-- Example for SQL Stored Procedure with Output Parameters USE [SQL Tutorial] GO CREATE PROCEDURE [dbo].[spCountEmployees] AS BEGIN RETURN (SELECT COUNT(EmpID)FROM [EmployeeDuplicates]) END |
OUTPUT
Let me execute the stored procedure.
|
1 2 3 4 5 6 7 8 9 10 11 |
-- Example for SQL Stored Procedure return Values USE [SQL Tutorial] GO -- 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 |
OUTPUT
Return Values in SQL Stored Procedure Example 3
In this example, Let me show you what will happen If we return 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).
|
1 2 3 4 5 6 7 8 9 10 |
-- Example for SQL Stored Procedure Return Values USE [SQL Tutorial] GO CREATE PROCEDURE [dbo].[spEmployeeName] AS BEGIN RETURN (SELECT [Occupation] FROM [EmployeeDuplicates] WHERE [LastName] = N'Gateway') END |
OUTPUT
Let me execute the stored procedure.
|
1 2 3 4 5 6 |
-- Example for SQL Stored Procedure Return Values USE [SQL Tutorial] GO DECLARE @EmpName VARCHAR(50) EXEC @EmpName = [dbo].[spEmployeeName] PRINT @EmpName |
OUTPUT
As you can see from the above screenshot that it is trowing an error. This is because, Return Values always return integers only.
Thank you for Visiting Our Blog
Leave a Reply