Search code examples
sql-serverstringsp-executesql

Dynamic sql and spexecutesql behavior

I have a table like

c1  c2    c3  c4
-----------------
2    1    7    13 
9    2    8    14
1    3    9    15
5    4   10    16
2    5   11    17
11   6   12    18

As in general I would not know the number of columns (in the code @d here 4) to get a string in the form:

2,9,1,5,2,11,  1,2,3,4,5,6,  7,8,9,10,11,12,  13,14,15,16,17,18

To do so I am doing:

 DECLARE  @d INT
         ,@counterI INT
         ,@template AS nvarchar(max) 

   SET  @d  = 4;       
   SET  @counterI   = 1;
   Set  @template = 'SELECT STUFF( 
                         (  SELECT  '','' + CAST([col] AS VARCHAR) FROM (';                        
   WHILE (@counterI < @d) BEGIN
        SET @template += ' SELECT [c'+CAST(@counterI-1 AS VARCHAR)+'] AS col FROM [MyTable] UNION ALL ';
        SET @counterI   = @counterI + 1; 
   END
  Set  @template += ' SELECT [c'+CAST(@counterI-1 AS VARCHAR)+'] AS col FROM [MyTable] ' 
  Set  @template += ') alldata FOR XML PATH('''')  )  ,   1  ,   1  ,  '''' )';    

declare @CommaString varchar(max)
set @CommaString = ''
exec sp_executesql @template, N'@CommaString varchar(max) out', @CommaString out

So if I do

select @CommaString;

enter image description here

Why is not @CommaString at the moment of selecting it returning the string if when doing the sp_executesql it is printing it right?

Solution

  • I may be missing something about how sp_executesql works, but don't you need something like 'SELECT @CommaString = ...' in @template, so that it assigns the comma string to the out parameter?

    Just to clarify, I think you need something like:

    DECLARE  @d INT
        ,@counterI INT
        ,@template AS nvarchar(max) 

SET  @d  = 4;       
SET  @counterI   = 1;
Set  @template = 'SELECT @CommaString = STUFF( 
                     (  SELECT  '','' + CAST([col] AS VARCHAR) FROM (';                        
WHILE (@counterI < @d) BEGIN
    SET @template += ' SELECT [c'+CAST(@counterI-1 AS VARCHAR)+'] AS col FROM [MyTable] UNION ALL ';
    SET @counterI   = @counterI + 1; 
END
Set  @template += ' SELECT [c'+CAST(@counterI-1 AS VARCHAR)+'] AS col FROM [MyTable] ' 
Set  @template += ') alldata FOR XML PATH('''')  )  ,   1  ,   1  ,  '''' )';    

declare @CommaString varchar(max)
set @CommaString = ''
exec sp_executesql @template, N'@CommaString varchar(max) out', @CommaString = @CommaString out

    As a simpler example, something like this is perhaps easier to read/see what I mean:

    declare @CommaString varchar(max)
set @CommaString = ''
exec sp_executesql 'SELECT @CommaString = ''1,2,3''', '@CommaString varchar(max) out', @CommaString = @CommaString out

    Incidentally, I've usually seen this kind of thing for string concatenation:

    DECLARE @MyString varchar(max)

SET @MyString = ''

SELECT @MyString = @MyString + ',' + MyColumn
  FROM MyTable