可变标量声明误差

Question

我有这样的疑问：

declare @cols AS NVARCHAR(MAX);  
declare @query  AS NVARCHAR(MAX);
declare @tahun AS VARCHAR(4);
declare @pks AS VARCHAR(2); 
declare @bulan AS VARCHAR(2);

set @tahun = '2016';
set @pks = '40';
set @bulan = '11';

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.asal) 
            FROM (
              SELECT
                cpo.tanggal,
                dg.asal,
                (((((cpo - ongkos_ms)* CAST(NULLIF(dg.minyak_efektif,0) as float) / (NULLIF(dg.tbs_diolah, 0))) +((pko - ongkos_is)* CAST(NULLIF(dg.inti_efektif,0) as float) / (NULLIF(dg.tbs_diolah, 0))))-(447.6 * ((CAST(NULLIF(dg.minyak_efektif,0) as float)  / (NULLIF(dg.tbs_diolah, 0)))+(CAST(NULLIF(dg.inti_efektif,0) as float)  / (NULLIF(dg.tbs_diolah, 0))))) )-(((((cpo - ongkos_ms)* CAST(NULLIF(dg.minyak_efektif,0) as float) / (NULLIF(dg.tbs_diolah, 0))) +((pko - ongkos_is)* CAST(NULLIF(dg.inti_efektif,0) as float) / (NULLIF(dg.tbs_diolah, 0))))-(447.6 * ((CAST(NULLIF(dg.minyak_efektif,0) as float)  / (NULLIF(dg.tbs_diolah, 0)))+(CAST(NULLIF(dg.inti_efektif,0) as float)  / (NULLIF(dg.tbs_diolah, 0)))))) * 0.02)) 
                as harga_beli_tbs_bersih
              FROM
                            PROD.dbo.[TBSP3.HargaCPOPKO] cpo
                        LEFT JOIN PROD.dbo.daily_GUUXX AS dg ON CONVERT (datetime, dg.tglolah, 103) = cpo.tanggal
                        LEFT JOIN PNL_TR_HARGA_KOMODITI AS ko ON ko.tanggal = cpo.tanggal
                        WHERE
                YEAR (cpo.tanggal) >= @tahun and pks=@pks and month(cpo.tanggal)=@bulan
            ) c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'');


set @query = 'SELECT tanggal, ' + @cols + ' from 
            (
                  SELECT
                  cpo.tanggal,
                  dg.asal,
                  (((((cpo - ongkos_ms)* CAST(NULLIF(dg.minyak_efektif,0) as float) / (NULLIF(dg.tbs_diolah, 0))) +((pko - ongkos_is)* CAST(NULLIF(dg.inti_efektif,0) as float) / (NULLIF(dg.tbs_diolah, 0))))-(447.6 * ((CAST(NULLIF(dg.minyak_efektif,0) as float)  / (NULLIF(dg.tbs_diolah, 0)))+(CAST(NULLIF(dg.inti_efektif,0) as float)  / (NULLIF(dg.tbs_diolah, 0))))) )-(((((cpo - ongkos_ms)* CAST(NULLIF(dg.minyak_efektif,0) as float) / (NULLIF(dg.tbs_diolah, 0))) +((pko - ongkos_is)* CAST(NULLIF(dg.inti_efektif,0) as float) / (NULLIF(dg.tbs_diolah, 0))))-(447.6 * ((CAST(NULLIF(dg.minyak_efektif,0) as float)  / (NULLIF(dg.tbs_diolah, 0)))+(CAST(NULLIF(dg.inti_efektif,0) as float)  / (NULLIF(dg.tbs_diolah, 0)))))) * 0.02)) 
                  as harga_beli_tbs_bersih
                FROM
                                    PROD.dbo.[TBSP3.HargaCPOPKO] cpo
                                LEFT JOIN PROD.dbo.daily_GUUXX AS dg ON CONVERT (datetime, dg.tglolah, 103) = cpo.tanggal
                                LEFT JOIN PNL_TR_HARGA_KOMODITI AS ko ON ko.tanggal = cpo.tanggal
                                WHERE
                  YEAR (cpo.tanggal) >= '+@tahun +' and pks='+ @pks +' and month(cpo.tanggal)='+@bulan+'
           ) x
            pivot 
            (
                 max(harga_beli_tbs_bersih)
                for asal in (' + @cols + ')
            ) p ';

exec (@query);
end

当我运行它时，我会得到一个错误：

Err 42000 - Server必须声明标量变量"@tahun“

我已经尝试过所有类型的变量声明，但我仍然会得到这个错误。我仍然不熟悉SQL查询。提前感谢！

Answer 1

您正在比较字符串和整数。

你能试试：

declare @tahun AS INT;
set @tahun = 2016;

和

 YEAR (cpo.tanggal) >= '+ CONVERT(varchar(4), @tahun) +' and pks='