How to get sp_executesql result into a variable?

Question

I have a piece of dynamic SQL I need to execute, I then need to store the result into a variable.

I know I can use sp_executesql but can't find clear examples around about how to do this.

Rotatory

Answer 1

If you have OUTPUT parameters you can do

DECLARE @retval int   
DECLARE @sSQL nvarchar(500);
DECLARE @ParmDefinition nvarchar(500);

DECLARE @tablename nvarchar(50)  
SELECT @tablename = N'products'  

SELECT @sSQL = N'SELECT @retvalOUT = MAX(ID) FROM ' + @tablename;  
SET @ParmDefinition = N'@retvalOUT int OUTPUT';

EXEC sp_executesql @sSQL, @ParmDefinition, @retvalOUT=@retval OUTPUT;

SELECT @retval;

But if you don't, and can not modify the SP:

-- Assuming that your SP return 1 value
create table #temptable (ID int null)
insert into #temptable exec mysp 'Value1', 'Value2'
select * from #temptable

Not pretty, but works.

Lienlienhard

my sp will be sp_executesql @myQuery – Rotatory

@retvalOUT=@retval OUTPUT? Shouldn't the third parameter of the sp_executesql be just @retval OUTPUT? – Crimmer

Just a tangential Question, What about more than one OUTPUT? what would be the Query look like? – Underbelly

@SrivastavReddy I don't get how can this answer get this much upvotes. Check Nishanth's answer.. – Protagoras

I think this is correct: EXEC sp_executesql @sSQL, @ParmDefinition, @retval OUTPUT; – Hidalgo

@sotn: The Nishanth's answer requires SQL Server 2008, wich, in Apr 09 (when I created this answer) wasn't all that common. – Lienlienhard

See Nishanth's answer - It is a better solution – Argillite

Ian is right, someone should edit this – Heeler

The solution perfectly works. Its also more relevant to the question. All these upvotes are well justified, in my opinion. – Reisman

Here's the link to the docs, I found this helpful. learn.microsoft.com/en-us/sql/relational-databases/… Key point to note: @parmDefinition MUST be nvarchar, otherwise it will throw errors. Tripped me up until I notice that. – Fennie

Answer 2

DECLARE @vi INT
DECLARE @vQuery NVARCHAR(1000)

SET @vQuery = N'SELECT @vi= COUNT(*) FROM <TableName>'

EXEC SP_EXECUTESQL 
        @Query  = @vQuery
      , @Params = N'@vi INT OUTPUT'
      , @vi = @vi OUTPUT

SELECT @vi

Swung

Where is the declaration of variable viOUTPUT and viINT ? – Catcher

This one worked for me. The answer with more votes didn't work – Concretion

Parameter @vQuery MUST be declared as NVARCHAR not VARCHAR. – Orsino

Though not required, its not a bad idea to keep the names of the parameters unique i.e. vi and viOut. – Fennie

Answer 3

DECLARE @tab AS TABLE (col1 VARCHAR(10), col2 varchar(10)) 
INSERT into @tab EXECUTE  sp_executesql N'
SELECT 1 AS col1, 2 AS col2
UNION ALL
SELECT 1 AS col1, 2 AS col2
UNION ALL
SELECT 1 AS col1, 2 AS col2'

SELECT * FROM @tab

Intermission

I've used this method before. It only seems to work on the first insert into @tab. If you try to insert into @tab and run multiple execute sp_executesql, with different sql, select * from @tab only shows the results of the first execute – Impeller

Oops, my bad. There was an error in my 2nd select, which meant it was returning zero rows. This method works great, and doesn't require a temp table! – Impeller

This is the best answer. Although I needed to run dynamic SQL, which means you need to build your dynamic SQL into a parameter first i.e. Declare @SQL nvarchar(255) = N'Select 20' - then simply sl_executeSql passing the parameter instead. – Argillite

This solution is the very best one, I tried many many others and this is the ONLY one that worked for me. Thank you Nishanth – Appetizer

Answer 4

DECLARE @ValueTable TABLE
    (
    Value VARCHAR (100)
    )

SELECT @sql = N'SELECT SRS_SizeSetDetails.'+@COLUMN_NAME+' FROM SRS_SizeSetDetails WHERE FSizeID = '''+@FSizeID+''' AND SRS_SizeSetID = '''+@SRS_SizeSetID+'''';

INSERT INTO @ValueTable
EXEC sp_executesql @sql;

SET @Value='';

SET @Value = (SELECT TOP 1  Value FROM @ValueTable)

DELETE FROM @ValueTable

Orvieto

Answer 5

Return values are generally not used to "return" a result but to return success (0) or an error number (1-65K). The above all seem to indicate that sp_executesql does not return a value, which is not correct. sp_executesql will return 0 for success and any other number for failure.

In the below, @i will return 2727

DECLARE @s NVARCHAR(500)
DECLARE @i INT;
SET @s = 'USE [Blah]; UPDATE STATISTICS [dbo].[TableName] [NonExistantStatisticsName];';
EXEC @i = sys.sp_executesql @s
SELECT @i AS 'Blah'

SSMS will show this Msg 2727, Level 11, State 1, Line 1 Cannot find index 'NonExistantStaticsName'.

Raviv

Answer 6

If you want to return more than 1 value use this:

DECLARE @sqlstatement2      NVARCHAR(MAX);
DECLARE @retText            NVARCHAR(MAX);  
DECLARE @ParmDefinition     NVARCHAR(MAX);
DECLARE @retIndex           INT = 0;

SELECT @sqlstatement = 'SELECT @retIndexOUT=column1 @retTextOUT=column2 FROM XXX WHERE bla bla';

SET @ParmDefinition = N'@retIndexOUT INT OUTPUT, @retTextOUT NVARCHAR(MAX) OUTPUT';

exec sp_executesql @sqlstatement, @ParmDefinition, @retIndexOUT=@retIndex OUTPUT, @retTextOUT=@retText OUTPUT;

returned values are in @retIndex and @retText

Gangue

Answer 7

Declare @variable int
Exec @variable = proc_name

Dennard

Answer 8

This worked for me:

DECLARE @SQL NVARCHAR(4000)

DECLARE @tbl Table (
    Id int,
    Account varchar(50),
    Amount int
) 

-- Lots of code to Create my dynamic sql statement

insert into @tbl EXEC sp_executesql @SQL

select * from @tbl

Photomural

Doesn't get much more straightforward than this. Worked for my purposes. Thanks – Cero

Answer 9

Here's something you can try

DECLARE  @SqlStatement  NVARCHAR(MAX) = ''
       ,@result     XML
       ,@DatabaseName  VARCHAR(100)
       ,@SchemaName    VARCHAR(10)
       ,@ObjectName    VARCHAR(200);

SELECT   @DatabaseName = 'some database'
       ,@SchemaName   = 'some schema'
       ,@ObjectName   = 'some object (Table/View)'

SET @SqlStatement = '
                    SELECT @result = CONVERT(XML,
                                            STUFF( ( SELECT *
                                                     FROM 
                                                       (
                                                          SELECT TOP(100) 
                                                          * 
                                                          FROM ' + QUOTENAME(@DatabaseName) +'.'+ QUOTENAME(@SchemaName) +'.' + QUOTENAME(@ObjectName) + '
                                                       ) AS A1 
                                                    FOR XML PATH(''row''), ELEMENTS, ROOT(''recordset'')
                                                 ), 1, 0, '''')
                                       )
                ';

EXEC sp_executesql @SqlStatement,N'@result XML OUTPUT', @result = @result OUTPUT;

SELECT DISTINCT
    QUOTENAME(r.value('fn:local-name(.)', 'VARCHAR(200)')) AS ColumnName
FROM @result.nodes('//recordset/*/*') AS records(r)
ORDER BY ColumnName

Emmanuel

Answer 10

Most answers in this post are vulnerable to sql injection, because they concatenate the input variables directly into the sql script. This is not a problem if you use a stored procedure without dynamic code, but even a stored procedure can be vulnerable if you use dynamic sql. Dynamic sql means that the sql script is compiled inside the stored procedure or passed to the stored procedure as a parameter; and thus newly compiled every time the stored procedure executes.

To protect yourself against sql injections, your input values must ALSO be introduced to the sql via parameters.

Here are three examples - the last two using stored procedures. The first stored procedure (example-2) does not use a dynamically built sql string, while the second stored procedure (example-3) does.

-- Example-1: Use sp_executesql with parameters (not in a stored procedure)
-- Note: INPUT parameters prevent sql injection
DECLARE @myDate DATE 
SET     @myDate = '2000-05-27';
DECLARE @dayOfYear INT;
    
EXEC sp_executesql 
@Stmt = N'SELECT @paramOutput = DATEPART(dy, @paramInput)',
@params = N'@paramInput DATE, @paramOutput INT OUTPUT',
@paramInput = @myDate,
@paramOutput = @dayOfYear OUTPUT;
    
Select @dayOfYear
    
    
-- Example-2: Concatenate parameters in a Stored Procedure that does not use dynamic sql
DROP PROCEDURE IF EXISTS dbo.SP_Scalar_NotDynamic;
GO
Create procedure dbo.SP_Scalar_NotDynamic (@myDate DATE, @dayOfYear INT OUTPUT)
As
    SELECT @dayOfYear = DATEPART(dy, @myDate)
    RETURN;
GO
    
-- Test SP_Scalar_NotDynamic
DECLARE @myDate DATE 
SET @myDate = '2000-05-27';
DECLARE @dayNumber INT;
EXECUTE dbo.SP_Scalar_NotDynamic @myDate, @dayOfYear = @dayNumber OUTPUT; 
SELECT @dayNumber


-- Example-3: Use sp_executesql with parameters in a Stored Procedure that uses dynamic sql
DROP PROCEDURE IF EXISTS dbo.SP_Scalar_Dynamic;
GO
Create procedure dbo.SP_Scalar_Dynamic (@myDate DATE, @mySql NVARCHAR(100), @dayOfYear INT OUTPUT)
As
    EXEC sp_executesql
    @mySql,
    @params      = N'@paramInput DATE, @paramOutput INT OUTPUT',
    @paramInput  = @myDate,
    @paramOutput = @dayOfYear OUTPUT;
    RETURN;
GO
    
-- Test SP_Scalar_Dynamic
DECLARE @myDate DATE 
SET     @myDate = '2000-05-27';
DECLARE @mySql NVARCHAR(100)
SET     @mySql = N'SELECT @paramOutput = DATEPART(dy, @paramInput)'
DECLARE @dayNumber INT;
EXECUTE dbo.SP_Scalar_Dynamic @myDate, @mySql, @dayOfYear = @dayNumber OUTPUT; 
SELECT  @dayNumber

Ine

Answer 11

This was a long time ago, so not sure if this is still needed, but you could use @@ROWCOUNT variable to see how many rows were affected with the previous sql statement.

This is helpful when for example you construct a dynamic Update statement and run it with exec. @@ROWCOUNT would show how many rows were updated.

Here is the definition

Extremadura

The @@ROWCOUNT will return zero if you called sp_executesql. That variable is useful indeed, but if you are calling sp_executesql you will need to combine the usage of @@ROWCOUNT and the output parameter of sp_executesql as shown in Eduardo Molteni's answer – Neglectful