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

User · Answer

DECLARE  vi INT DECLARE  vQuery NVARCHAR 1000   SET  vQuery   N SELECT  vi  COUNT    FROM  lt TableName gt    EXEC SP EXECUTESQL           Query     vQuery          Params   N  vi INT OUTPUT           vi    vi OUTPUT  SELECT  vi

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

Declare  variable int Exec  variable   proc name

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[tsql] How to get sp_executesql result into a variable?

Examples related to tsql

Examples related to dynamic-sql