Stored procedures are some of the most complex objects we have available to us in Microsoft SQL Server. There are many tips and tricks you need to know when creating and using them in your databases.
In this very brief tutorial, we’ll talk about one of the more confusing things about stored procedures: Output parameters.
If you need an introduction to stored procedures, check out the full beginner-friendly tutorial here:
SQL Server Stored Procedure: The Ultimate Guide for Beginners
Without further ado, let’s get into it.
Understanding stored procedure output parameters:
We’ll create a very simple stored procedure that accepts two numbers as input parameters, then multiplies those two numbers and returns the result back to the caller.
In the definition of the procedure, we start with outlining the two input parameters:
CREATE PROCEDURE getProduct(@number1 INT, @number2 INT)
In the body of this procedure, we’ll write the product of these two numbers to a variable:
CREATE PROCEDURE getProduct (@number1 INT, @number2 INT) AS SELECT @returnValue = @number1*@number2
Notice we haven’t actually declared that @returnValue variable yet.
The question becomes: How do we return the value of this variable back to the caller?
Answer: By outlining that variable as an OUTPUT parameter in the parameter list of the procedure. Basically like so:
CREATE PROCEDURE getProduct (@number1 INT, @number2 INT, @returnValue INT OUTPUT) AS SELECT @returnValue = @number1*@number2
This will declare the @returnValue variable, and the keyword ‘OUTPUT‘ means we want to return the value of this variable back to the caller.
Alright, we’re done here.
How to call a stored procedure with an output parameter:
I think the more tricky part of a stored procedure with output parameters is how you actually call that procedure.
The most important thing to remember is that we need to create a variable that absorbs the return value from the stored procedure. Let’s go ahead and create a very simple variable:
DECLARE @productOfTwoNumbers AS INT
Next, we’ll call the procedure and outline the two input parameters to start with:
DECLARE @productOfTwoNumbers AS INT EXECUTE getProduct @number1 = 10, @number2 = 20
Ok, easy enough.
The procedure has a third parameter, which is of course the @returnValue parameter that is supposed to hold the product of the two numbers. When we call the procedure, we also outline this third parameter, and we set it to the name of our absorbing variable. Like so:
DECLARE @productOfTwoNumbers AS INT EXECUTE getProduct @number1 = 10, @number2 = 20, @returnValue = @productOfTwoNumbers
Lastly, we need to outline the keyword ‘OUTPUT‘ again after this parameter to identify this parameter specifically as an output parameter:
declare @productOfTwoNumbers AS INT EXECUTE getProduct @number1 = 10, @number2 = 20, @returnValue = @productOfTwoNumbers OUTPUT
Folks, that’s all there is to it!
Now when we execute this procedure, the product of those two numbers will be absorbed into the @productOfTwoNumbers variable. If we want to see the value in that variable, we can run a very simple SELECT statement against it:
Easy peasy.
Next Steps:
Leave a comment if you found this tutorial helpful!
If you found this tutorial helpful, you should consider downloading the following FREE Ebook:
FREE Ebook on SQL Server Stored Procedures!
This FREE Ebook discusses all the key points you need to understand about stored procedures in Microsoft SQL Server. Everything discussed in this tutorial can be found in the Ebook. It will definitely be an excellent resource for you to keep and reference throughout your career as a data professional. Make sure you get it today!
If you need a full rundown on stored procedures, check out the full beginner-friendly tutorial found here:
SQL Server Stored Procedure: The Ultimate Guide for Beginners
Also, Are you making these 3 mistakes in your stored procedures?
Thank you very much for reading!
Make sure you subscribe to my newsletter to receive special offers and notifications anytime a new tutorial is released!
If you have any questions, leave a comment. Or better yet, send me an email!
