How to write a stored procedure with OUTPUT parameter in SQL Server
by GetCodeSnippet.com • May 27, 2013 • Microsoft SQL Server • 0 Comments
Well, you can return a single value or multiple values using select statement in stored procedure but you may return a value using OUTPUT parameter of stored procedure. I will explain how we can achieve this task. In one of my previous article, I have explained about how we can write a stored procedure.
Let’s see a stored procedure with OUTPUT parameter.
1 2 3 4 5 6 7 8 9 10 11 |
CREATE PROCEDURE dbo.GetTotalOrders ( @CustomerID INT, @TotalOrders INT OUTPUT ) AS BEGIN SELECT @TotalOrders = COUNT(*) FROM dbo.Orders WHERE CustomerID = @CustomerID END |
I have used two parameters in above stored procedure. First parameter “@CustomerID” is normal INPUT parameter but second parameter “@TotalOrders” is an OUTPUT parameter and it will return a value. I am actually returning total number of orders by a specific customer. I have used only one OUTPUT parameter but you can specify as many OUTPUT parameters as you want.
Now let’s see how we can execute the stored procedure with OUTPUT parameter.
1 2 3 |
DECLARE @TotalOrders INT EXECUTE dbo.GetTotalOrders 5, @TotalOrders OUTPUT SELECT @TotalOrders |
First we need to declare variable to get the OUTPUT parameter value. Then we have to mention OUTPUT keyword with this variable to execute the store procedure. At the end we can select this variable. If you are using multiple OUTPUT parameters, you have to declare variable for each OUTPUT parameter.
