CODEWITHSUNDEEP
t-sqldynamic-sql
JohnIdol
JohnIdol

Reputation: 50097

How to get sp_executesql result into a variable?

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.

Upvotes: 215

Views: 370235

Answers (11)

Gerhard Liebenberg
Gerhard Liebenberg

Reputation: 444

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

Upvotes: 1

Abhishek Dheeman
Abhishek Dheeman

Reputation: 71

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

Upvotes: 5

Mark Hedley
Mark Hedley

Reputation: 199

Declare @variable int
Exec @variable = proc_name

Upvotes: 3

Nishanth
Nishanth

Reputation: 537

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

Upvotes: 52

RecursiveCTE
RecursiveCTE

Reputation: 53

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

Upvotes: 1

Buchaiah
Buchaiah

Reputation: 655

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

Upvotes: 63

Dylan Hayes
Dylan Hayes

Reputation: 2366

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

Upvotes: 1

cihadakt
cihadakt

Reputation: 3214

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

Upvotes: 6

bazsano1
bazsano1

Reputation: 31

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

Upvotes: 0

PseudoToad
PseudoToad

Reputation: 1574

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

Upvotes: 4

Eduardo Molteni
Eduardo Molteni

Reputation: 39413

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.

Upvotes: 313

Related Questions