title: Stored procedure to update temp table based on Date in SQL Server
body: <p>I have two tables :</p>
  
  <pre><code>Teams [Id],[name],[nomatches],[owngoals],[othergoals],[points]
  </code></pre>
  
  <p>and</p>
  
  <pre><code>Matches[id],[homeid],[outid],[homegoal],[outgoal],[matchdate]
  </code></pre>
  
  <p>I have a trigger which fires on <code>INSERT,UPDATE,DELETE</code> so the <code>Teams</code> table current score table is always updated.</p>
  
  <p>Example :</p>
  
  <pre><code>Select * from teams;
  </code></pre>
  
  <p>Result :</p>
  
  <pre><code>Name     NumberOfMatches   OwnGoals  OtherGoals  Points
  -------------------------------------------------------    
  FC Chelsea      33            61          22        68
  FC Barcelona    33            46          34        59
  FC Man UD       33            57          50        52
  </code></pre>
  
  <p>The problem:</p>
  
  <p>Table <code>Matches</code> has a column <code>matchdate</code>. I want to recalculate the current score table (with my trigger maybe) for all games played before the entered date.</p>
  
  <p>I don't know how to create a temp table to store the re-calculated data (nomaches, owngoals, othergoals, points for each team) based on the <code>Date</code> parameter.</p>
  
  <p>What I have so far :</p>
  
  <pre><code>CREATE PROCEDURE check_scoretable  
  ( 
      @pDate DATE = NULL
  )
  as
  DECLARE @Date DATE = COALESCE(@pDate,GETDATE())
      declare @homeid char(3);
      declare @outid char(3);
      declare @id int;
  
          SELECT * INTO #temp_table2  FROM teams;
          SET NOCOUNT ON; -- copy of the teams table 
  
  declare cur CURSOR LOCAL for 
  select homeid, outid
  from matches where matches.matchdate &lt; @Date
   open cur
   fetch next from cur into @homeid, @outid
   while @@FETCH_STATUS = 0 BEGIN
      select @homeid;
      select @outid;
      --Increment number of matches
      update #temp_table2 set #temp_table2.nomatches = #temp_table2.nomatches+1 where #temp_table2.id = @homeid;
      update #temp_table2 set #temp_table2.nomatches = #temp_table2.nomatches+1 where #temp_table2.id = @outid;
  
     fetch next from cur into @homeid, @outid
  END
  close cur
  deallocate cur
  
  -- Test the stored procedure
  DECLARE @d DATETIME
  SELECT @d = GETDATE()
  EXEC check_scoretable  @date = @d
  </code></pre>