I had someone ask me about this the other day. Specifically getting variable data out of a dynamic SQL statement. I should point out here that I really enjoy dynamic SQL. I find it fairly intuitive and always enjoy the challenge, so I’ve gotten pretty good at it. So I just kind of threw an answer out there. Unfortunately, that didn’t clear things up. So I had to find another way to explain the subject. Which brings us to the teaching/blogging advice of the day. This person is very senior, knows SQL far better than me in a lot of areas. Dynamic SQL just isn’t one of them. The moral being, never be afraid to blog about a subject no matter how easy it sounds to you. Just because it’s easy to you doesn’t mean it makes sense to someone else.

Getting back on topic, the discussion I had with them gave me an idea for (what I think) is a really easy way to understand how to use input and output variables with dynamic SQL.

Let’s start with the basic syntax.

DECLARE @sql nvarchar(max)

SET @sql = N'SELECT * FROM sys.databases'

EXEC sp_executesql @sql

Then if I want to pass in a single parameter.

DECLARE @NamePat nvarchar(50) = 'm%';
DECLARE @sql nvarchar(max);

SET @sql = N'SELECT * FROM sys.databases ' + 
	' WHERE name LIKE @InternalNamePat';

EXEC sp_executesql @sql, N'@InternalNamePat nvarchar(50)', 
	@InternalNamePat = @NamePat

Quick point here. The first two parameters (the command, and the parameter list) must be Unicode (hence the N at the front). Also, notice how I named the variable inside the dynamic SQL “Internal”? That is completely not necessary. It can be exactly the same name as the external variable. I just did that to make it more obvious which variable was which.

Now let’s add an output variable.

DECLARE @NamePat nvarchar(50) = 'm%';
DECLARE @MaxCreateDate datetime;
DECLARE @sql nvarchar(max);

SET @sql = N'SELECT @InternalMaxCreateDate = MAX(create_date) ' + 
	' FROM sys.databases WHERE name LIKE @InternalNamePat';

EXEC sp_executesql @sql, N'@InternalNamePat nvarchar(50), @InternalMaxCreateDate datetime OUTPUT', 
	@InternalNamePat = @NamePat, @InternalMaxCreateDate = @MaxCreateDate OUTPUT;

PRINT @MaxCreateDate

Now here you’ll see that I’m declaring another variable. And again I’m declaring it twice. First in the main part of the code, next in the second parameter of sp_executesql. You’ll also notice that in the second declaration I declared it as an OUTPUT variable. Then I also used OUTPUT in that last bit where I pass in the variable.

Now, for some, that’s going to be sufficient. For others, it’s still going to be highly confusing. So let’s look at the same thing from a slightly different viewpoint.

CREATE PROCEDURE dynamicsql (/* param 2 */ @InternalNamePat nvarchar(50), 
		@InternalMaxCreateDate datetime OUTPUT /* param 2 */ ) as
/* param 1 */SELECT @InternalMaxCreateDate = MAX(create_date) 
FROM sys.databases WHERE name LIKE @InternalNamePat /* param 1 */
GO

DECLARE @NamePat nvarchar(50) = 'm%';
DECLARE @MaxCreateDate datetime;

EXEC dynamicsql /* param 3 */ @InternalNamePat = @NamePat, 
	@InternalMaxCreateDate = @MaxCreateDate OUTPUT; /* param 3 */

PRINT @MaxCreateDate;

You’ll see this is a pretty basic CREATE PROCEDURE statement. You should also see that in the places where I have the /* */ comments I literally copied and pasted the parameters from the sp_executesql statement. (Yes, I realize what I’m calling Param 3 is actually parameters 3 and 4. In fact, it could be 3, 4, 5, 6, 7…. It’s still basically one piece of the process.)

And in the end it breaks down like this:

• Param 1: The code to run.
• Param 2: The parameter decleration for the dynamic SQL.
• Param 3: The parameter assignment when calling the dynamic SQL.

 
With the OUTPUT statement in appropriate places, as needed.