title: Stored Procedure error "Must declare the scalar variable"
body: <p>I have written a stored procedure to extract data from a table in SQL Server:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>Upon executing this, I get this error: </p>
  
  <blockquote>
    <p>Must declare the scalar variable "@FirstSundayOfMonth"</p>
  </blockquote>
  
  <p>As you can see in the code, I have declared and initialized the value of <code>@FirstSundayOfMonth</code> in the stored procedure. </p>
  
  <p>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. </p>
  
  <p>I'm guessing I'm missing something minor but I'm unable to figure out what it is. </p>
  
  <p>Any leads on this would be great!</p>