Avoid this sp_executesql issue

Articles, Tech / By

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

DECLARE @DynamicCMD NVARCHAR(500),
	@DynamicResult NVARCHAR(500);

SET @DynamicCMD = N'SELECT CAST(YEAR(GETDATE()) AS VARCHAR(4))';
EXEC sp_executesql @DynamicCMD, N'@DynamicResult varchar(400) OUTPUT', @DynamicResult = @DynamicResult OUTPUT
SELECT @DynamicResult;

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:

DECLARE @DynamicResult NCHAR(4);
SELECT @DynamicResult = CAST(YEAR(GETDATE()) AS NCHAR(4));
SELECT @DynamicResult AS ThisYear;

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.

DECLARE @DynamicCMD NVARCHAR(500),
 @DynamicResult NVARCHAR(500);

SET @DynamicCMD = N'SELECT @DynamicResult = CAST(YEAR(GETDATE()) AS VARCHAR(4))';
EXEC sp_executesql @DynamicCMD, N'@DynamicResult varchar(400) OUTPUT', @DynamicResult = @DynamicResult OUTPUT
SELECT @DynamicResult;

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.