Stored Procedure error "Must declare the scalar variable"

Question

I have written a stored procedure to extract data from a table in SQL Server:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[csp_OperatorVarianceMonthlyView]
-- =============================================
-- Procedure: csp_OperatorVarianceMonthlyView
--
-- Summary: Produce the result table that is needed for the Operator Variance Tracker report.
--
-- Params: @RetailStoreId - Store Id on which the results need to be filtered
--         @StartDate - The start business day date for which results are to be collected (NULL = Today)
--         @EndDate - The end business day date for which results are to be collected (NULL = Today)
--         @OperatorIDList - OperatorID comma seperated list

-- Returns:   A result set to populate a Operator Variance Report tab
--
-- Version: 2013-06-25 11:00
-- ==============================================
    @TenantID       t_ID,
    @RetailStoreID  t_ID,
    @StartDate      t_Timestamp = null,
    @EndDate        t_Timestamp = null,
    @OperatorIDList varchar(500)=null
AS
    IF 1 = 0 
    BEGIN
        SET FMTONLY OFF
    END
BEGIN
    declare @SQL                    nvarchar(2500)
    declare @StartBusinessDayDate   t_TimeStamp
    declare @EndBusinessDayDate     t_TimeStamp
    declare @StartDateOfYear        t_TimeStamp
    declare @StartDateOfMonth       t_TimeStamp
    declare @Sunday                 t_TimeStamp
    declare @FirstSundayOfMonth     t_TimeStamp

    SET NOCOUNT ON;

    if (@StartDate is null)
        select @StartDate = GETDATE()

    if (@EndDate is null)
        select @EndDate = GETDATE()

    -- Eliminate any time information that might be in the Start/End dates
    select @StartBusinessDayDate = convert(varchar(8), cast (@StartDate as datetime), 112)
    select @EndBusinessDayDate   = convert(varchar(8), cast (@EndDate   as datetime), 112)

    -- Compute StartDateOfYear and StartDateOfMonth to compute the YTD and MTD variances
    set @StartDateOfYear = DATEADD(year, DATEDIFF(year, 0, @EndBusinessDayDate),0)
    set @StartDateOfMonth = DATEADD(month, DATEDIFF(month, 0, @EndBusinessDayDate), 0)

    -- Select a Sunday 
    set @Sunday = '2017-01-01' 
    -- Get first Sunday of the month for getting week-wise data for the OVT report
    set @FirstSundayOfMonth = DATEADD(WEEK, DATEDIFF(WEEK, @Sunday, @StartBusinessDayDate), @Sunday)  

-- Create temporary tables to hold the monthly and yearly variance amounts for each operator
Create table #TempOperatorVarianceYTD ([OperatorID] int, [VarianceAmtYTD] decimal)
Create table #TempOperatorVarianceMTD ([OperatorID] int, [VarianceAmtMTD] decimal)

-- Insert Year To Date (YTD) variance amounts by operator to the tempoarary table
Insert into #TempOperatorVarianceYTD 
Select OperatorID, sum(NetOverShortAmount)
from TillBalanceExceptionPTDSummary
where BusinessDayDate between @StartDateOfYear and @EndBusinessDayDate
and RetailStoreID = @RetailStoreID
and PeriodTypeCode = 1
group by OperatorID

-- Insert Month To Date (MTD) variance amounts by operator to the tempoarary table
Insert into #TempOperatorVarianceMTD 
Select OperatorID, sum(NetOverShortAmount)
from TillBalanceExceptionPTDSummary
where BusinessDayDate between @StartDateOfMonth and @EndBusinessDayDate
and RetailStoreID = @RetailStoreID
and PeriodTypeCode = 1
group by OperatorID

-- Build the SQL statement to get the result table for the monthly view of the Operator Variance Tracker report. 
select @SQL = 'Select TBES.OperatorID as OperatorID, ' +
              'O.[Name] as OperatorName, ' +
              'YTD = YTD.VarianceAmtYTD, ' +
              'MTD = MTD.VarianceAmtMTD, ' +
              'Week1 = SUM(CASE WHEN TBES.PeriodTypeCode = 2 AND TBES.BusinessDayDate = @FirstSundayOfMonth THEN TBES.NetOverShortAmount ELSE 0 END), ' +
              'Week2 = SUM(CASE WHEN TBES.PeriodTypeCode = 2 AND TBES.BusinessDayDate = DATEADD(WEEK, 1, @FirstSundayOfMonth) THEN TBES.NetOverShortAmount ELSE 0 END), ' +
              'Week3 = SUM(CASE WHEN TBES.PeriodTypeCode = 2 AND TBES.BusinessDayDate = DATEADD(WEEK, 2, @FirstSundayOfMonth) THEN TBES.NetOverShortAmount ELSE 0 END), ' +
              'Week4 = SUM(CASE WHEN TBES.PeriodTypeCode = 2 AND TBES.BusinessDayDate = DATEADD(WEEK, 3, @FirstSundayOfMonth) THEN TBES.NetOverShortAmount ELSE 0 END), ' +
              'Week5 = SUM(CASE WHEN TBES.PeriodTypeCode = 2 AND TBES.BusinessDayDate = DATEADD(WEEK, 4, @FirstSundayOfMonth) THEN TBES.NetOverShortAmount ELSE 0 END) ' +
              'FROM [postrn001].[dbo].[TillBalanceExceptionPTDSummary] TBES ' +
              'join [posfdn001].[dbo].[Operator] O ' +
              'on TBES.OperatorID = O.OperatorID ' +
              'join #TempOperatorVarianceYTD YTD ' +
              'on O.OperatorID = YTD.OperatorID ' +
              'join #TempOperatorVarianceMTD MTD ' +
              'on O.OperatorID = MTD.OperatorID ' +
              'where TBES.RetailStoreID = @RetailStoreID '
-- Add the operator filter if necessary
if ( NOT (@OperatorIDList is null) )
    begin
        -- Avoid sql injection by cleaning the string
        set @OperatorIDList = REPLACE(@OperatorIDList, ';', '')
        set @OperatorIDList = REPLACE(@OperatorIDList, ')', '')
        set @OperatorIDList = REPLACE(@OperatorIDList, '-', '')
        select @SQL = @SQL + 'and TBES.OperatorID IN (' + @OperatorIDList + ') '
    end
--Add the group by clause
select @SQL = @SQL + 'group by TBES.OperatorID, O.[Name], YTD.VarianceAmtYTD, MTD.VarianceAmtMTD'

print @SQL
print @FirstSundayOfMonth

exec sp_executesql @SQL,
             N'@TenantID t_ID, @RetailStoreId t_ID, @StartBusinessDayDate t_TimeStamp, @EndBusinessDayDate t_TimeStamp, @OperatorIDList varchar(500)',
             @TenantID, @RetailStoreId, @StartBusinessDayDate, @EndBusinessDayDate, @OperatorIDList

return @@error

-- Drop the temporary tables. 
drop table #TempOperatorVarianceMTD, #TempOperatorVarianceYTD

END

Upon executing this, I get this error:

Must declare the scalar variable "@FirstSundayOfMonth"

As you can see in the code, I have declared and initialized the value of @FirstSundayOfMonth in the stored procedure.

I'm not sure on what I might be missing here - I tried copying the query part to a separate window and executing it after assigning the values to the declared variables and it executes and generates the result that I expect.

I'm guessing I'm missing something minor but I'm unable to figure out what it is.

Any leads on this would be great!

Answer 1

The variable declared in the outer scope is not visible to the dynamic SQL statement. Pass @FirstSundayOfMonth as another parameter to sp_executesql.