mysql - 必须声明标量变量

Question

我正在使用这个简单的代码来旋转某些列。

但由于该错误，我无法使其工作。

DECLARE @IO_dy AS VARCHAR(100) = '>9C604-M'
DECLARE @style_dy AS VARCHAR (100) = 'S1415MBS06'
DECLARE @query AS VARCHAR(8000)
DECLARE @con AS VARCHAR(8000)
SET @con = STUFF((SELECT distinct ',' + QUOTENAME(Size_id) 
                  FROM iPLEXSTY_SIQ 
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

SET @query = 'SELECT *
FROM (
    SELECT DISTINCT a.Po_no,a.Article_id,a.Season_id,a.Customer_id,a.Destn_id,b.planned_dt, (c.Description + c.Resource_id) AS comb_size,a.Qty,a.Size_id
    from iPLEXSTY_SIQ a
    INNER JOIN iPLEX_BULK_PO_DET b on b.upload_batch_id = a.Batch_id  
    INNER JOIN iPLEXCOLORS c on c.Seq_no = a.Seq_no 
    WHERE IO_no = @IO_dy AND Style_id = @style_dy       //ERROR HERE
    GROUP BY a.Po_no,a.Article_id,a.Season_id,a.Customer_id,a.Destn_id,b.planned_dt,(c.Description + c.Resource_id),a.Qty,A.Size_id
) as s

PIVOT
(
    SUM(Qty)
    FOR Size_id IN (' +@con+ ')
)AS pvt'    

EXEC(@query)

Answer 1

补充另一个答案仅对查询中使用的变量名称使用单引号

DECLARE @IO_dy AS VARCHAR(100) = '>9C604-M'
DECLARE @style_dy AS VARCHAR (100) = 'S1415MBS06'
DECLARE @query AS VARCHAR(8000)
DECLARE @con AS VARCHAR(8000)
SET @con = STUFF((SELECT distinct ',' + QUOTENAME(Size_id) 
                  FROM iPLEXSTY_SIQ 
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

SET @query = 'SELECT *
FROM (
    SELECT DISTINCT a.Po_no,a.Article_id,a.Season_id,a.Customer_id,a.Destn_id,b.planned_dt, (c.Description + c.Resource_id) AS comb_size,a.Qty,a.Size_id
    from iPLEXSTY_SIQ a
    INNER JOIN iPLEX_BULK_PO_DET b on b.upload_batch_id = a.Batch_id  
    INNER JOIN iPLEXCOLORS c on c.Seq_no = a.Seq_no 
    WHERE IO_no = '''+@IO_dy+''' AND Style_id = '''+@style_dy+'''       //ERROR HERE
    GROUP BY a.Po_no,a.Article_id,a.Season_id,a.Customer_id,a.Destn_id,b.planned_dt,(c.Description + c.Resource_id),a.Qty,A.Size_id
) as s

PIVOT
(
    SUM(Qty)
    FOR Size_id IN (' +@con+ ')
)AS pvt'    

EXEC(@query)

正如另一个答案解释的那样，使用 default 关键字将值设置为 varchar