The W Blog

Variable output with dynamic SQL

I've had a couple conversations about this subject this week so I thought I would post on this topic.  There are occasions where we need the output of a dynamic query placed into a variable for later use.  Today we will look at the stored procedure sp_executesql and show how you can utilize this system stored procedure with output variables.

If you are not familiar with sp_executesql you are probably aware of the EXEC() statement for processing dynamic queries.  sp_executesql is the preferred choice for this type of processing as it has the following benefits:

• Parameter substitution is supported
• Execution plans are created, making for more efficient processing
• The execution string does not have to be rebuilt for each execution

More detail can be found on sp_executesql at BOL.  Here's a quick example of sp_executesql in action:

--Create test table
IF OBJECT_ID('dbo.test', 'U') IS NOT NULL
DROP TABLE dbo.test;
GO
CREATE TABLE dbo.test
(
testID        INT            NOT NULL
,testValue    VARCHAR(50)    NOT NULL
);

--Populate test table
INSERT dbo.test (testID, testValue) VALUES (1, 'One');
INSERT dbo.test (testID, testValue) VALUES (2, 'Two');
INSERT dbo.test (testID, testValue) VALUES (3, 'Three');
INSERT dbo.test (testID, testValue) VALUES (4, 'Four');
INSERT dbo.test (testID, testValue) VALUES (5, 'Five');

--Set up parameters
DECLARE @queryString    NVARCHAR(MAX);
DECLARE @intVariable    INT;
DECLARE @parameterDef    NVARCHAR(500);
DECLARE @outputTestID    VARCHAR(50);

SET @queryString = 'SELECT @testValueOUT = testValue 
FROM dbo.test 
WHERE testID = @testID';
SET @intVariable = 5;
SET @parameterDef = '@testID INT, @testValueOUT VARCHAR(50) OUTPUT';

--Execute the query string
EXECUTE sp_executesql 
@queryString
,@parameterDef
,@testID = @intVariable
,@testValueOUT = @outputTestID OUTPUT;

--Return the output variable
SELECT @outputTestID AS [variableOutput];

--Clean up after yourself
DROP TABLE dbo.test;

The query string is a simple select from our table dbo.test.  You will notice we are already placing two variables in the query string, one to accept our input parameter and another to receive the output of our query.  The parameters are defined in the @parameterDef variable.  Here is where we see the parameter substitution referenced earlier.  If we were using the EXEC() statement we would have to either CAST or CONVERT our input, which is certainly possible, but opens up potential coding errors and type conversion issues.

To execute the query string we make our call to sp_executesql passing in the query string, the parameter definitions, and intialization of each variable used.

If we were to place the execution of the stored procedure and selection of @outputTestID into a loop for all values in the dbo.test table we could easily substitute the values into the query for processing without any additional work.  We would also see that the query plan is reused for the query.  We can check this with the following query of the dynamic management view sys.dm_exec_cached_plans:

--Check the plan cache for reuse
SELECT
    usecounts
   ,cacheobjtype
   ,objtype
   ,[text] AS batchText
FROM
    sys.dm_exec_cached_plans
    CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE
    usecounts > 1
    AND cacheobjtype = 'Compiled Plan'
ORDER BY
    usecounts DESC ;

Another tool in your T-SQL tool belt.  Enjoy!

**Update:  I had to add a note saying that this is certainly a solution to a specific problem.  Please make sure you validate input for any dynamic SQL you use in your application.  SQL Injection is a nasty thing to have happen to a database application.  Okay, end of public service announcement.  :)