Must declare the scalar variable in stored procedure in SQL Server

Question

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.

This is the error after I execute the stored procedure:

Msg 137, Level 15, State 2, Line 30
Must declare the scalar variable "@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

Side note: you should **not** use the `sp_` prefix for your stored procedures. Microsoft has [reserved that prefix for its own use (see *Naming Stored Procedures*)](http://msdn.microsoft.com/en-us/library/ms190669%28v=sql.105%29.aspx), and you do run the risk of a name clash sometime in the future. [It's also bad for your stored procedure performance](http://www.sqlperformance.com/2012/10/t-sql-queries/sp_prefix). It's best to just simply avoid `sp_` and use something else as a prefix - or no prefix at all! — marc_s, Aug 24 '17 at 04:42
Set [Bad Habits to kick - putting NOLOCK everywhere](http://blogs.sqlsentry.com/aaronbertrand/bad-habits-nolock-everywhere/) - it is *not recommended* to use this everywhere - quite the contrary! — marc_s, Aug 24 '17 at 04:42
Possible duplicate of [Must declare the scalar variable](https://stackoverflow.com/questions/7181976/must-declare-the-scalar-variable) — TT., Aug 24 '17 at 06:07
Thank you for the advises. I will keep that as my learning as I am new to SQL Server. — Eliseo Jr, Aug 24 '17 at 11:33

score 0 · Answer 1 · answered Aug 24 '17 at 03:43

0

should be

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

and your select should be

SET @query = N'SELECT * FROM

as nvarchar

answered Aug 24 '17 at 03:43

RoMEoMusTDiE

4,739
1
17
26

Close. He should probably still pass @BU to sp_executesql as it's own variable. – Joel Coehoorn Aug 24 '17 at 03:48
yup... my solution was just the quickest tweak otherwise define the parameters – RoMEoMusTDiE Aug 24 '17 at 03:52

Must declare the scalar variable in stored procedure in SQL Server

1 Answers1