Avoid this sp_executesql issue

If you don’t use this feature very often, you can come across some unexpected sp_executeSQL issues. This time we’ll look at an sp_excuteSQL output issue.

If you don’t use this feature very often, you can come across some unexpected sp_executeSQL issues.  In fact, I’ll probably use this blog the most because I don’t use it that often and I have to look up the specifics every time.

Let’s start with a code sample so we can see what happens when we run it.

Setting up the sp_executeSQL issue

Above you’ll see some really simple sp_executeSQL syntax.  All it does is to parse out the YEAR from the current date.  And you’ll see that I’m taking that result and putting it into a variable called @DynamicResult.  Finally in line 6 I select the variable to see my value.  But the results aren’t what I expect.  Take a look at the results and you’ll see what I mean:

sp_executesql issueThe problem here is clear; I’m getting two resultsets! The first one has the value I’m after, and the other one is NULL.  And looking at it, the NULL value is the one that’s supposed to be the variable.  So how do I get the value into my variable, and get this down to a single result set?  The answer is that you have to treat it like it’s not inside the sp_execute at all.

Understanding the sp_executeSQL issue

Here’s what I mean.  Let’s assume you’re not running this command in sp_executeSQL.  Let’s assume you just want to run it as a regular query like this:

Now this is something you’re a bit more familiar with.  You declare the variable, then you set it equal to the result of your function call.  Then you select it.  We have no problem filling our variable when we do it this way.  And that’s the way you have to do it inside your dynamic code to avoid this sp_executesql issue.  And just for completeness, here’s the result:
spexecuteresults3

Fixing the sp_executeSQL issue

It’s easy now to see what needs to be done.  All we have to do is make sure that we use that same syntax inside the procedure call and we’ll avoid this sp_executesql issue.  Take a look at the fix and you’ll see that it’s exactly like the code above where we just ran the straight statement.

If you didn’t notice, the difference is in the dynamic query itself.

Okay, that’s it.  All you have to do is make sure that you output that exact variable and you’re golden.  You can see that the variable I’m setting inside the dynamic SQL string is the same one I’ve got marked as OUTPUT.

These types of features in SQL are very handy but they all have pitfalls and nuances.  And learning how and when to use them can be tricky because the solution in one case won’t be the solution in another case.

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.

 

Using SQL Server OUTPUT Parameters

Let’s talk about using SQL Server OUTPUT parameters!

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.

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

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:

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.

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.

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.

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.

Learn SQL Server OUTPUT Parameters

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.

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:

  1. 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.
  2. 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.

SQL Server Output Parameters

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.