Last time we talked about where OUTPUT parameters are useful. This time, let’s talk about using SQL Server OUTPUT parameters. So let’s get started.
OUTPUT parameter syntax
For starters, a lot of people hate the OUTPUT parameter syntax. They think it’s bulky and too much trouble. Honestly, I used to think that too, only now I’ve got enough experience with them that it’s not that bad. Or maybe I’ve been beat down by it so much that I don’t even notice anymore. Well, whichever it is, here’s the syntax for using SQL Server OUTPUT parameters.
USE tempdb; GO --Start by creating an SP. CREATE PROCEDURE SP1 ( @Name VARCHAR(50) OUTPUT ) AS SET @Name = €˜Returned value.' GO --Declare the var you need to hold the results from the SP. DECLARE @NameBackFromSP VARCHAR(50); --Call the SP and set the results. EXEC SP1 @Name = @NameBackFromSP OUTPUT; SELECT @NameBackFromSP; DROP PROCEDURE SP1;
In the above code I’m creating a stored procedure named SP1. That procedure has a single parameter that’s declared as an OUTPUT parameter. This much you know from the last post. Now, starting on line 16, I declare the variable that’s going to pick up the value. I’ve given it a name that lets you know exactly what it’s going to be doing.
Here’s a screenshot of the code if you’d prefer:
OUTPUT Parameters have Backwards Syntax
And here’s where everybody’s hate for this topic kicks in. I call the procedure in line 22 and I assign the variable to the OUTPUT parameter. However, let’s take a step back and see why this is so strange to everybody. When you normally assign a value to a variable, it usually goes left to right like this:
DECLARE @I int; SET @I = 20;
So you assign variables to values like in the code above €¦ from left to right. However, in the original code block, it’s backwards. The value for @NameBackupFromSP comes from the left. And this is essential to understanding how to use OUTPUT parameters. When you assign them, the assignment is backwards from what you do in every other language. This is a by-product of mixing methods and here, the stored procedure syntax had to win. Here’s what I mean. When you pass in values to stored procedures, you always assign the values from right to left, like you would expect.
EXEC MySP @Name = €˜Maria', @Zip = 89778, @Age = 54;
So the above is what you would expect to see when doing regular assignment to a stored procedure. And that’s what I mean when I said above that the stored procedure syntax had to win. No matter what, when calling a stored procedure with parameters, you have to use this syntax (assuming you’re naming your variables, that is). So when you go to use OUTPUT parameters, the same stored procedure syntax holds true. Only now you’re signifying that you also want to get that value back out as well.
EXEC SP1 @Name = @NameBackFromSP OUTPUT;
So we assign the @Name parameter the same way we do if it were a regular assignment, only now we use the OUTPUT keyword to tell SQL that we want to also get that value back out. And of course, nothing is going to happen visibly unless we select the variable so we can see it.
EXEC SP1 @Name = @NameBackFromSP OUTPUT; SELECT @NameBackFromSP;
That’s the basics of how to call a stored procedure with OUTPUT parameters. Next time we’ll get into more details of how to use this feature.
Article 1: Learn SQL Server OUTPUT Parameters
Download our FREE maintenance modules:
- 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.