将值分配给存储过程中的变量

2020-02-21 sql sql-server tsql stored-procedures ssms-2017
 select  @numOfColumns =  count(distinct Col) from #b
 SET @sql2=
'SELECT'+ @columns +'+= QUOTENAME(Col) + '',''
 from (SELECT DISTINCT top @numOfColumns Col FROM #b ORDER BY Col) A';

 EXECUTE sp_executesql @sql2;

我正在尝试使此存储过程正常工作。尝试将@numOfColumns传递给该语句,然后将QUOTENAME(Col)中的值分配给@columns,然后执行该语句。

脚本

DECLARE 
@columns NVARCHAR(MAX) = '',
@sql     NVARCHAR(MAX) = '',
 @sql2     NVARCHAR(MAX) = '',
@numOfColumns int = 0;

SELECT customerid, curbal,  Col = CAST (ROW_NUMBER() OVER (PARTITION BY 
convert(int,customerid) ORDER BY convert(float,curbal) desc) as int)
into #b
FROM [sav acc]

select  @numOfColumns =  count(distinct Col) from #b

SET @sql2= 'SELECT '+ @columns+' += QUOTENAME(Col) + '','' from (SELECT 
DISTINCT top (@numOfColumns) Col FROM #b ORDER BY Col) A';

EXECUTE sp_executesql @sql2, N'@numOfColumns', @numOfColumns int;

这是帖子的来源。 原始帖子 。该解决方案确实有效,但是我使用源数据对其进行了测试,该数据将列定位在[3],[2], 1处 ,并且我希望列为1 ,[2],[3]

Answers

首先,您使用TOP方式毫无意义。如果#b Col有5个不同的值,那么SELECT DISTINCT Col FROM #b将仅返回5条记录,无论您是否应用TOP 5 。看来您只是在使用它来允许排序,但是您可以将排序放在外部查询中。

其次,您不应该使用变量赋值来连接字符串, 这种行为是不可靠的并且没有文档说明,您应该使用STRING_AGG ,或者对于SQL Server的早期版本,您可以利用XML扩展 ,或者:

CREATE TABLE #B (Col CHAR(1));
INSERT #B (Col) VALUES ('A'), ('A'), ('B'), ('C');

DECLARE @Columns NVARCHAR(MAX) = 
    (   SELECT  STRING_AGG(QUOTENAME(Col), ',') WITHIN GROUP (ORDER BY Col)
        FROM    (SELECT DISTINCT Col FROM #B) AS b
    );

要么

DECLARE @Columns NVARCHAR(MAX) = 
        STUFF(( SELECT  CONCAT(',', QUOTENAME(Col))
                FROM    #B
                GROUP BY Col
                ORDER BY Col
                FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)'), 1, 1, '');

尽管上面显示了动态sql是不必要的,但是如果您使用@top ,也将不必要,您可以简单地使用:

DECLARE  @numOfColumns INT = (SELECT count(distinct Col) from #b);
DECLARE @Columns NVARCHAR(MAX) = '';

SELECT  @Columns += QUOTENAME(Col) + ','
FROM    (SELECT DISTINCT TOP (@numOfColumns) Col FROM #B) AS b
ORDER BY Col;

SELECT @Columns;

Related