必须在SQL Server中的存储过程中声明标量变量

Could anyone have idea why my stored procedure encountered an error when I executed it?

任何人都可以知道为什么我的存储过程在执行时遇到错误？

I have tried to isolate in a simple select statement and supplying the value in where clause, it works fine. But when I put to stored procedure with a declared parameter, it won't work anymore.

我试图在一个简单的select语句中隔离并在where子句中提供值，它工作正常。但是当我使用声明的参数放入存储过程时，它将不再起作用。

This is the error after I execute the stored procedure:

这是执行存储过程后的错误：

Msg 137, Level 15, State 2, Line 30
Must declare the scalar variable "@BU".

消息137，级别15，状态2，行30必须声明标量变量“@BU”。

See below for my stored procedure.

请参阅下面的存储过程。

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[sp_Planning_GetMonthlyPivotX]
    @BU char(7)
AS
BEGIN
    DECLARE @PivotColumns AS NVARCHAR(MAX)

    SELECT @PivotColumns = COALESCE(@PivotColumns + ',', '') + QUOTENAME([YEARMONTH]) 
    FROM 
        (SELECT DISTINCT  
             CONVERT(CHAR(6), CAST([releaseddate] AS DATE), 112) AS [YEARMONTH] 
         FROM 
             [dbo].[ShopOrder]) AS PivotQuery 
    ORDER BY 
        [YEARMONTH] 

    DECLARE @query NVARCHAR(MAX)

    SET @query = 'SELECT * 
                  FROM 
                      (SELECT 
                           a.[ItemCode], b.[Description],
                           B.[Catalog] as [BU], a.[WH],
                           f.[CustNo], f.[CustName],
                           b.[IsOutSource],
                           [dbo].[f_Routings_GetMCHr](a.[ItemCode]) as [Std.Time],
                           d.[Tonnage], d.[MachineNo], 
                           d.[MachineNo] as [FixedMachine],
                           [dbo].[f_BOM_GetChildItem](a.ItemCode) as [ChildItem],
                           [dbo].[f_Item_GetItemDescription]([dbo].[f_BOM_GetChildItem](a.ItemCode)) as [ChildDescription],
                           [dbo].[f_BOM_GetChildQty](a.ItemCode,[dbo].[f_BOM_GetChildItem](a.ItemCode)) as [Usage],
                           e.[TRP],
                           CONVERT(char(6), cast([ReleasedDate] as date),112) as [YearMonthRel],
                           [dbo].[f_Inventory_GetTotalStock](b.[ItemCode]) as [Inventory],
                           b.[MinStockLvl] as [SafetyStock],
                           a.[RequiredQty] as [ShopOrderQty],
                           a.[FinishedQty] as [ActualQty],
                           g.[WorkTimeHr] as [AvailableTime]
                       FROM 
                           [CPS].[dbo].[ShopOrder] AS a with(nolock) 
                       LEFT OUTER JOIN
                           [CPS].[dbo].[items] AS b with(nolock) ON a.[ItemCode] = b.[ItemCode] 
                       LEFT OUTER JOIN
                           [CPS].[dbo].[ItemByMachine] AS d with(nolock) ON a.[ItemCode] = d.[ItemCode] 
                       LEFT OUTER JOIN
                           [CPS].[dbo].[MCTonnage] as e with(nolock) ON d.[Tonnage] = e.[Tonnage] 
                       LEFT OUTER JOIN
                           [CPS].[dbo].[Customer] as f with(nolock) ON b.[CustNo] = f.[CustNo] 
                       LEFT OUTER JOIN
                           [CPS].[dbo].[Machine] as g with(nolock) ON d.[MachineNo] = g.[MachineNo]
                       WHERE 
                           b.[Catalog] = @BU) AS T
             PIVOT 
             (
                 SUM([ShopOrderQty])
                 FOR [YearMonthRel] IN (' + @PivotColumns + ')
            ) p order by [Tonnage],[MachineNo],[ItemCode];' 

    Execute SP_EXECUTESQL @query
END

1 个解决方案

#1

should be

应该

WHERE b.[Catalog] = ''' + @BU + '''

and your select should be

你的选择应该是

SET @query = N'SELECT * FROM

as nvarchar

作为nvarchar

#1