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.

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.

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.