There are plenty of sites out there that show the syntax of using SQL Server OUTPUT parameters in your SPs, but not many take the time to explain the syntax in detail, or even why you should care. Here I’m going to show you why OUTPUT parameters are a great way to achieve your programming goals. This is going to be a multi-part series.
SQL Server OUTPUT Parameters: The Why
There are a couple reasons why you’d want to use OUTPUT parameters:
- You need to get a couple solid values out of the procedure. This is a common requirement and something that a lot of people use functions for. And let’s face it, functions are very handy for giving you scalar values. However, functions also have some pretty strict guidelines; you can’t do anything that generates log activity, for example. So in these cases, where you need scalar data, but a function won’t do, you can switch to a stored procedure with OUTPUT parameters.
- You’re trying to pass data from one procedure to another, but you’re running into the ‘nested INSERT…EXEC’ issue. This issue happens when you have an INSERT…EXEC in say SP1, and then you call SP2 from SP1, but SP2 also contains an INSERT…EXEC. SQL Server won’t allow this and believe me when I say it’s a huge pain because there are some things you simply can’t reasonable do without INSERT…EXEC. So, so avoid this nesting of INSERT…EXEC, you can use OUTPUT parameters.
SQL Server OUTPUT Parameters: The How
Creating SQL Server OUTPUT parameters is easy, but the syntax isn’t intuitive at all. So I’m going to talk you through the syntax of creating a procedure with an OUTPUT parameter, and then in the next post we’ll talk about how to call that procedure and get the values we need.
So again, SQL Server OUTPUT parameters are basically used in lieu of a function when either the function restrictions are too great, or when you just need better performance.
Therefore, to create a stored procedure with an OUTPUT parameter, you simply need to define it as OUTPUT in the parameter section like this:
CREATE PROCEDURE SP1
(
Name varchar(20),
Zip varchar(10),
Phone varchar(20) OUTPUT
)
AS
…
You can see that we have 3 INPUT parameters, and even though the OUTPUT is the only one that’s actually marked, the others are definitely INPUT parameters. The default parameter direction is INPUT, so it doesn’t get marked. Therefore, only the OUTPUT gets marked. When we call this procedure we’ll pass in the Name and Zip we’re interested in, and we’ll get back the Phone. So if I wanted more than one piece of information, then I would specify more than one OUTPUT parameter like this:
CREATE PROCEDURE SP1
(
Name varchar(20),
Zip varchar(10),
Phone varchar(20) OUTPUT,
CustomerID int OUTPUT
)
AS
…
SQL Server OUTPUT Parameters: The Practice
Here’s a screenshot from one of our procedures in our FREE maintenance modules to show you how this will look IRL.
Ok, that’s really all there is to creating a stored procedure with an OUTPUT parameter. And next time we’ll talk about how to get the parameter value into your calling procedure.
Article 2: Using OUTPUT Parameters
You can download our FREE maintenance modules below:
Minion Backup: Free SQL Server Backup utility with world-class enterprise features and full lifecycle management.
Minion Reindex: Free SQL Server Reindex utility with unmatched configurability.
Minion CheckDB: Free SQL Server CHECKDB that solves all of your biggest CHECKDB problems.