title: 'Invalid column name' from within a stored procedure
body: <p>I wrote a stored procedure to insert future dates (excluding Sat and Sun) into a permanent table. The routine uses just one temp table. The purpose of this post is not to critique the Stored Procedure. Although, I am certain it could be improved. However, the purpose of this post is to analyze why the stored procedure is throwing these errors when called under one set of circumstances and not others.</p>
  
  <p>1 - Here are the errors I receive</p>
  
  <pre><code>Msg 207, Level 16, State 1, Procedure MAKE_FUTURE_DATES, Line 25
  Invalid column name 'tdate'.
  Msg 207, Level 16, State 1, Procedure MAKE_FUTURE_DATES, Line 31
  Invalid column name 'wday'.
  </code></pre>
  
  <p>2 - Here is the stored procedure</p>
  
  <pre><code>ALTER PROCEDURE [dbo].[MAKE_FUTURE_DATES] (@STARTDATE DATE)
  AS
  BEGIN
  -- We need to populate FUTURE_DATES (table) with forward looking dates (week days only) 
  -- We do not consider/exclude holidays here. We just exclude Sat/Suns
  
  -- Temp table to hold the dates and days of the week
      CREATE TABLE #TMP_DATES(
          [tdate] [date] NULL,
          [wday] [varchar](10) NULL,  
          )
  
      -- To generate 'enough' future dates loop up to 1199 days in the future
      -- and insert dates that start with the current date and increase with each loop
  
      DECLARE @Loop INT
      SET @Loop = 0
      WHILE @Loop &lt; 1200
      BEGIN
          INSERT INTO #TMP_DATES (tdate) VALUES (DATEADD(weekday,@Loop,@STARTDATE))   
          SET @Loop = @Loop + 1
      END
  
      -- Now update the wday column with the weekday name so we can get rid of
      -- Sat/Sun in the next step
  
      UPDATE #TMP_DATES
      SET wday = UPPER(LEFT(DATENAME(dw,tdate),3))
  
      -- Get rid of Sat/Sun
      DELETE FROM #TMP_DATES WHERE wday = 'SAT' or wday = 'SUN'
  
      -- Now clear the final destination table
      TRUNCATE TABLE FUTURE_DATES
  
      -- Insert the weekday dates into future_dates
  
      INSERT INTO FUTURE_DATES (fdate,wday)
      SELECT tdate,wday FROM #TMP_DATES
  
      DROP TABLE #TMP_DATES
  </code></pre>
  
  <p>3 - I have been calling the above stored procedure within another stored procedure as a SQL Server task in the background (via the SQL Server job scheduler) for about 6 months without any errors or problems. More recently, I created a new stored procedure, let's call it 'ABC', that calls a stored procedure that calls MAKE_FUTURE_DATEs. </p>
  
  <p>4 - Here is the part I am trying to solve. When ABC is invoked as a SQL Server task in the background (via the SQL Server job scheduler) it throws the errors every time (which is daily) and the results are not produced/it crashes. When I first start up SQL Server Management Studio and run ABC it sometimes throws the error the first time. The second time in this sequence and all subsequent times it does not throw the error. Also, keep in mind that the stored procedure that has been calling MAKE_FUTURE_DATES for 6 months is still quite happy with no errors.</p>
  
  <p>I am looking for suggestions on how to debug this or what to look for. Particularly how can it throw the error sometimes and not others?</p>