Get Output From Dynamic SQL Query

Posted: March 10, 2009 in SQL Server 2005

To get the output value of a dynamic SQL query, open a new query window and execute the following line of code:

-- Declare variables
DECLARE @lvSQL         NVARCHAR(2000) -- This MUST be NVARCHAR!!!
DECLARE @liCount       INT;
DECLARE @liCountOutput INT;

-- Create temp table
CREATE TABLE [MyTable] ([col1] VARCHAR(10));

-- Insert records into temp table
INSERT INTO [Mytable] ([col1]) VALUES ('1');
INSERT INTO [Mytable] ([col1]) VALUES ('2');
INSERT INTO [Mytable] ([col1]) VALUES ('3');

-- Build the dynamic SQL
SET @lvSQL = 'SELECT @liCount = COUNT(*) FROM [Mytable]';

-- Call system stored procedure to execute the dynamic SQL
EXEC SP_EXECUTESQL @lvSQL, N'@liCount INT OUTPUT', @liCount OUTPUT;

-- Assign the output parameter
SET @liCountOutput = @liCount;

-- Select the output value
SELECT @liCountOutput;

-- Drop temp table
DROP Table [MyTable];
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s