Assign result of dynamic sql to variable

Question

I m doing dynamic SQL to convert all columns in a table a string  so After after all I do  EXEC  template      where  template is the dynamic generated query so    col1  col2 col3 --------------- 1    7    13  2    8    14 3    9    15 4   10    16 5   11    17 6   12    18    this results  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18   How do I assign to a variable the resulting string  something like   DECLARE   result AS varchar max    SET  result   EXEC  template

User · Answer

You could use sp executesql instead of exec   That allows you to specify an output parameter   declare  out var varchar max   execute sp executesql      N select  out var     hello world          N  out var varchar max  OUTPUT         out var    out var output  select  out var    This prints  hello world

User · Answer

Sample to execute an SQL string within the stored procedure    I m using this to compare the number of entries on each table as first check for a regression test  within a cursor loop   select  SqlQuery1   N select  CountResult1    select isnull count    0  from      DatabaseFirst   dbo    ObjectName        execute sp executesql     SqlQuery1   N  CountResult1 int OUTPUT        CountResult1    CountResult1 output

User · Answer

Most of these answers use sp executesql as the solution to this problem   I have found that there are some limitations when using sp executesql  which I will not go into  but I wanted to offer an alternative using EXEC     I am using SQL Server 2008 and I know that some of the objects I am using in this script are not available in earlier versions of SQL Server so be wary   DECLARE  CountResults TABLE  CountReturned INT  DECLARE       SqlStatement VARCHAR 8000     SELECT COUNT    FROM table         Count INT  INSERT  CountResults EXEC  SqlStatement   SET  Count    SELECT CountReturned FROM  CountResults  SELECT  Count

User · Answer

You should try this while getting SEQUENCE value in a variable from the dynamic table     DECLARE  temp table   temp varchar  MAX    DECLARE  SeqID nvarchar 150   DECLARE  Name varchar 150     SET  Name    Select Name from table  SET  SeqID    SELECT NEXT VALUE FOR     Name     Sequence  insert  temp exec   SeqID   SET  SeqID    select   from  temp   PRINT  SeqID   Result    1 row s  affected   1

User · Answer

You can use sp executesql with output parameter   declare  S nvarchar max     select  x   1   declare  xx int set  xx   0  exec sp executesql  S  N  x int out    xx out  select  xx   Result    No column name  1   Edit  In my sample  S is instead of your  template  As you can see I assign a value to  x so you need to modify  template so it internally assigns the comma separated string to the variable you define in your second argument to sp executesql  In my sample N  x int out   You probably want a varchar max  output parameter  Something like N  Result varchar max  out   Here is another example building a comma separated string from master  spt values  declare  template nvarchar max  set  template     select  Result    cast number as varchar 10         from master  spt values where type     P      declare  CommaString varchar max  set  CommaString       exec sp executesql  template  N  Result varchar max  out    CommaString out  select  CommaString

[sql-server] Assign result of dynamic sql to variable

Examples related to sql-server

Examples related to string