动态 sql 和 spexecutesql 行为

Question

我有一个像一般一样的表，

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

我不知道要获取以下形式的字符串的列数（在代码@d中为4）：

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

为此，我正在这样做：

 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

所以如果我这样做

select @CommaString;

如果在执行 sp_executesql 时，为什么在选择它时不返回字符串 @CommaString打印对了吗？

Answer 1

我可能遗漏了有关 sp_executesql 如何工作的一些信息，但是您不需要 @template 中的 'SELECT @CommaString = ...' 之类的东西，以便它将逗号字符串分配给 out 参数吗？

只是为了澄清，我认为你需要类似的东西：

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

作为一个更简单的例子，这样的东西可能更容易阅读/明白我的意思：

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

顺便说一句，我通常看到这种字符串连接的东西：

DECLARE @MyString varchar(max)

SET @MyString = ''

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