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.

Eduardo Molteni · Accepted Answer · 2009-04-29T17:40:06.680

285

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.

edited Apr 29 '09 at 17:40

answered Apr 29 '09 at 16:40

Eduardo Molteni

37,908
23
138
203

my sp will be sp_executesql @myQuery – JohnIdol Apr 29 '09 at 21:19
1

`@retvalOUT=@retval OUTPUT`? Shouldn't the third parameter of the `sp_executesql` be just `@retval OUTPUT`? – Mohammad Dehghan Oct 28 '13 at 13:46
2

Just a tangential Question, What about more than one OUTPUT? what would be the Query look like? – Srivastav Apr 13 '15 at 14:43
3

@SrivastavReddy I don't get how can this answer get this much upvotes. Check Nishanth's answer.. – sotn May 02 '17 at 12:22
3

I think this is correct: `EXEC sp_executesql @sSQL, @ParmDefinition, @retval OUTPUT;` – Ian Oct 11 '17 at 10:03
@sotn: The Nishanth's answer requires SQL Server 2008, wich, in Apr 09 (when I created this answer) wasn't all that common. – Eduardo Molteni Aug 11 '18 at 01:33
See Nishanth's answer - It is a better solution – Josh Harris Dec 03 '18 at 14:02
Ian is right, someone should edit this – Henrov Oct 11 '21 at 07:02

score 53 · Answer 2 · edited Feb 15 '19 at 15:13

53

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

edited Feb 15 '19 at 15:13

The_Black_Smurf

5,104
14
51
71

answered Oct 20 '09 at 13:35

Buchaiah

555
4
2

8

Where is the declaration of variable viOUTPUT and viINT ? – Jeson Martajaya Aug 30 '13 at 18:59
1

This one worked for me. The answer with more votes didn't work – sh_kamalh Dec 19 '15 at 15:09
5

Parameter @vQuery **MUST** be declared as NVARCHAR not VARCHAR. – Farzad Karimi Jul 27 '16 at 11:59

score 52 · Answer 3 · edited Mar 26 '20 at 10:08

52

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

edited Mar 26 '20 at 10:08

wishmaster

1,269
2
12
20

answered Apr 07 '11 at 08:23

Nishanth

529
4
2

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 – Mike Causer May 03 '12 at 06:08
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! – Mike Causer May 03 '12 at 06:10
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. – Josh Harris Dec 03 '18 at 14:01
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 – MMEL May 08 '20 at 05:22

score 4 · Answer 4 · answered Jun 19 '14 at 20:35

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'.

score 3 · Answer 5 · edited Jan 07 '21 at 00:36

3

Declare @variable int
Exec @variable = proc_name

edited Jan 07 '21 at 00:36

Pang

9,073
146
84
117

answered May 24 '13 at 14:47

Mark Hedley

199
2
8

score 3 · Answer 6 · answered Jun 09 '17 at 10:41

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

score 2 · Answer 7 · edited Jan 07 '21 at 00:37

2

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

edited Jan 07 '21 at 00:37

Pang

9,073
146
84
117

answered Nov 21 '15 at 11:06

Abhishek Dheeman

41
2

score 1 · Answer 8 · answered Dec 18 '19 at 18:35

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

score 0 · Answer 9 · answered Aug 29 '14 at 08:01

0

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

answered Aug 29 '14 at 08:01

bazsano1

31
1
4

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](https://stackoverflow.com/a/803234/2265446) – Cleptus Sep 09 '21 at 07:27

score 0 · Answer 10 · answered Oct 25 '18 at 21:30

0

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

answered Oct 25 '18 at 21:30

Dylan Hayes

2,271
1
22
33

How to get sp_executesql result into a variable?

10 Answers10

Linked

Related