In this article we will show you, How to use Output Parameters in SQL Stored Procedure , Or How to use Output Parameters to return a Value or 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.
In order to output a value from stored procedure you have to declare a variable along with OUT or OUTPUT keyword. For this demonstration, We are going to use the below shown SQL table
Output Parameters in SQL Stored Procedure Example 1
In this example we will show you, How to use Output Parameters in a Stored procedures. Please refer Select Stored Procedure article to understand, How to write Select statement inside a stored procedure.
Below statement will create a Stored procedure of Name spEmployeeCount. And we declare a variable called @EmployeeCount of integer Type along with OUTPUT keyword. Within the procedure, we are using the COUNT to find the number of employees in Employee Duplicate table, and then we are assigning the value to the Output parameter.
|
1 2 3 4 5 6 7 8 9 10 |
-- Example for SQL Stored Procedure with Output Parameters USE [SQL Tutorial] GO CREATE PROCEDURE [dbo].[spEmployeeCount] @EmployeeCount INT OUTPUT AS BEGIN SELECT @EmployeeCount = COUNT(EmpID)FROM [EmployeeDuplicates] END |
OUTPUT
Let me execute the stored procedure. Here, we have to pass the output parameter @EmployeeCount parameter.
|
1 2 3 4 5 6 7 8 9 10 11 |
-- Example for SQL Stored Procedure with Output Parameters USE [SQL Tutorial] GO -- Declare a Variable of Type Int. Should match with SP's Output parameter DECLARE @TotalEmployees INT -- Don't forget OUTPUT keyword EXEC [dbo].[spEmployeeCount] @TotalEmployees OUTPUT -- Printing the Output PRINT @TotalEmployees |
OUTPUT
Output Parameters in SQL Stored Procedure Example 2
In this example we will show you, How to use both Input and Output Parameters in a single Stored procedures. Please refer Input Parameters in a Stored Procedure article to understand, How to use input parameters in a stored procedure.
Here, we declare a variable called @LastName as input parameter, and @EmployeeProfession of Varchar type as Output parameter. Within the procedure, we are finding the Occupation of an employee whose last name is equal to input parameter. Next, we are assigning the value to the Output parameter.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
-- Example for SQL Stored Procedure with Output Parameters USE [SQL Tutorial] GO CREATE PROCEDURE [dbo].[spEmployeeProfession] @LastName VARCHAR(50), @EmployeeProfession VARCHAR(50) OUTPUT AS BEGIN SELECT @EmployeeProfession = [Occupation] FROM [EmployeeDuplicates] WHERE [LastName] = @LastName END |
OUTPUT
Let me execute the stored procedure. Here, we have to pass the value for the @LastName as Gateway.
|
1 2 3 4 5 6 7 |
-- Example for SQL Stored Procedure with Output Parameters USE [SQL Tutorial] GO DECLARE @Profession VARCHAR(50) EXEC [dbo].[spEmployeeProfession] N'Gateway', @Profession OUTPUT PRINT 'Gateway is Working in the ' + @Profession |
OUTPUT
Output Parameters in SQL Stored Procedure Example 3
In this example we will show you, How to use Output Parameters in a Stored procedures to return multiple outputs. Here, we declare a variable called @LastName as input parameter, and three Output parameter. Within the procedure, we are finding the Occupation, Education, and yearly Income of an employee whose last name is equal to input parameter. Next, we are assigning the value to the Output parameter.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
-- Example for SQL Stored Procedure with Output Parameters USE [SQL Tutorial] GO CREATE PROCEDURE [dbo].[spEmployeeProf] @LastName VARCHAR(50), @Profession VARCHAR(50) OUTPUT, @Education VARCHAR(50) OUTPUT, @Income FLOAT OUTPUT AS BEGIN SELECT @Profession = [Occupation], @Education = Education, @Income = YearlyIncome FROM [EmployeeDuplicates] WHERE [LastName] = @LastName END |
OUTPUT
Let me execute the stored procedure.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
-- Example for SQL Stored Procedure with Output Parameters USE [SQL Tutorial] GO DECLARE @EmpProfession VARCHAR(50), @EmpEducation VARCHAR(50), @EmpIncome FLOAT EXEC [dbo].[spEmployeeProf] N'Gateway', @EmpProfession OUTPUT, @EmpEducation OUT, @EmpIncome OUT SELECT @EmpProfession AS Profession, @EmpEducation AS Education, @EmpIncome AS Income |
OUTPUT
Let me show you, What will happen if you miss the OUTPUT keyword
As you can see from the above screenshot, it is returning NULL value. So, don’t forget the OUTPUT keyword.
Thank you for Visiting Our Blog
Leave a Reply