動態 SQL 中斷語句

我正在嘗試編寫一個查詢，該查詢以某個列為中心，其中可能包含任意數量的不同值。我可以讓它與靜態值一起使用，但是當我嘗試使用動態 sql 參數化查詢時它會中斷。這是行之有效的聲明。

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(record_name) 
           FROM ams_data
           WHERE report_name = 'class_and_component_prices'
           AND commodity ='whey'
           FOR XML PATH(''), TYPE
           ).value('.', 'NVARCHAR(MAX)') 
       ,1,1,'')


-- construct dynamic SQL
SET @sql ='
SELECT * FROM   
(
   SELECT 
       record_name,
       value,
       date,
       commodity
   FROM 
      ams_data
       WHERE report_name = ''class_and_component_prices''
       AND commodity = ''whey''
) t 
PIVOT(
   max(value)  
   FOR record_name IN ('+ @cols +')
)  AS pivot_table ;';

EXECUTE sp.executesql @sql

這是我的帶有參數的版本。

ALTER PROCEDURE  [dbo].[pivot_record]
(
   -- Add the parameters for the stored procedure here
   @tablename VARCHAR(max) = nass_data,
   @report_name VARCHAR(max) = class_and_component_prices,
   @commodity VARCHAR(max) = whey
)
AS
       -- SET NOCOUNT ON added to prevent extra result sets from
       -- interfering with SELECT statements.
       SET NOCOUNT ON
       DECLARE @cols AS NVARCHAR(MAX),
               @sql  AS NVARCHAR(MAX),
               @actualTable AS VARCHAR(MAX)

       SELECT @actualTable = QUOTENAME( TABLE_NAME )
       FROM INFORMATION_SCHEMA.TABLES
       WHERE TABLE_NAME = @tablename

       SET @sql = '
       STUFF((
           SELECT distinct '','' + QUOTENAME(record_name) 
           FROM ' + @actualTable + ' 
           WHERE report_name = ''' + @report_name + '''
           AND commodity = ''' + @commodity + ''' 
           FOR XML PATH(''''), TYPE
           ).value(''.'', ''NVARCHAR(MAX)'') 
           ,1,1,'''')'        

       EXECUTE sp_executesql @sql, N'@cols VARCHAR(MAX) OUTPUT',@cols = @cols OUTPUT;

       -- construct dynamic SQL
       SET @sql ='
       SELECT * FROM   
       (
           SELECT 
               record_name,
               value,
               date,
               commodity

           FROM 
              ' + @tablename + '
               WHERE report_name = ' + @report_name + '
               AND commodity = ' + @commodity + '
       ) t 
       PIVOT(
           max(value)  
           FOR record_name IN ('+ @cols +')
       )  AS pivot_table ;';

       -- execute the dynamic SQL
       EXECUTE sp_executesql @sql;

當我嘗試執行它時，我從第一個 sql 語句中得到一個錯誤。在嘗試執行並將結果儲存在 @cols 之前，我列印了 sql 語句，以確保它與我的第一個有效語句完全相同。

我假設動態 sql 的一些怪癖導致了這個問題，但我們將不勝感激。

編輯：我發現我需要在 STUFF 子句之前包含一個 SELECT ，但我仍然沒有得到我想要的結果，現在它只輸出一個空表而不是正確的透視數據。

第一個查詢有效，因為 STUFF 語句不是動態的，所以 @cols 變數使用正確填充SET @cols = 。

在您的第二個查詢中，您從未將變數設置為 STUFF 值，因此會出現錯誤。

原來的：

SET @sql = '
       STUFF((
           SELECT distinct '','' + QUOTENAME(record_name) 
           FROM ' + @actualTable + ' 
           WHERE report_name = ''' + @report_name + '''
           AND commodity = ''' + @commodity + ''' 
           FOR XML PATH(''''), TYPE
           ).value(''.'', ''NVARCHAR(MAX)'') 
           ,1,1,'''')'        

       EXECUTE sp_executesql @sql, N'@cols VARCHAR(MAX) OUTPUT',@cols = @cols OUTPUT;

新的：

SET @sql = 'SET @cols = STUFF((
           SELECT distinct '','' + QUOTENAME(record_name) 
           FROM ' + @actualTable + ' 
           WHERE report_name = ''' + @report_name + '''
           AND commodity = ''' + @commodity + ''' 
           FOR XML PATH(''''), TYPE
           ).value(''.'', ''NVARCHAR(MAX)'') 
           ,1,1,'''')'        

       EXECUTE sp_executesql @sql, N'@cols VARCHAR(MAX) OUTPUT',@cols = @cols OUTPUT;

如果沒有看到第二個@SQL 或@cols 的輸出，就很難進行調試。我將從那裡開始調試，選擇/列印變數並複制並粘貼執行它

或嘗試不同的方式？

DECLARE @ColumnName AS NVARCHAR(MAX)
SELECT @ColumnName= ISNULL(@ColumnName + ',','') + QUOTENAME(Date)
FROM (SELECT DISTINCT [Date] FROM #Final) AS Weeks

僅供參考，這是我的 PIVOT 查詢

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)

--Get distinct values of the PIVOT Column 
SELECT @ColumnName= ISNULL(@ColumnName + ',','') + QUOTENAME(Date)
FROM (SELECT DISTINCT [Date] FROM #Final) AS Weeks

--Prepare the PIVOT query using the dynamic 
SET @DynamicPivotQuery = '
select *
from 
(
 select SQLInstanceName, Date, node_name
 from #Final
 WHERE 1=1 AND  Has_Changed = ''Y''
) src
pivot
(
 MIN(node_name)
 for [Date] in ('  + @ColumnName + ')
) piv
order by 1
'
--SELECT @DynamicPivotQuery --debug
EXEC sp_executesql @DynamicPivotQuery

引用自：https://dba.stackexchange.com/questions/269767