title: Update view and use a stored procedure for it
body: <p>I have created a view. I want to create a stored procedure to update the view and use info("penalty" column... link- <a href="http://rextester.com/GHQW83226" rel="nofollow noreferrer">http://rextester.com/GHQW83226</a>)  from view and add it to another table.</p>
  
  <p>View:</p>
  
  <pre><code>create VIEW consecutive
  as
  WITH cte as (
      SELECT *, 
             LAG([pointsRewarded], 1) OVER (PARTITION BY [EmployeeID] ORDER BY [WeekNumber]) as prev1_points,
             LAG([pointsRewarded], 2) OVER (PARTITION BY [EmployeeID] ORDER BY [WeekNumber]) as prev2_points,
             LAG([pointsRewarded], 3) OVER (PARTITION BY [EmployeeID] ORDER BY [WeekNumber]) as prev3_points
      FROM week1
  )
  SELECT *,
         CASE WHEN [pointsRewarded] = -10 AND prev1_points = -10  AND prev2_points = -10  AND prev3_points = -10 
              THEN -200
              WHEN [pointsRewarded] = -10 AND prev1_points = -10  AND prev2_points = -10 
              THEN -100
              WHEN [pointsRewarded] = -10 AND prev1_points = -10
              THEN -50
              ELSE 0
         END penalty       
  FROM cte
  </code></pre>
  
  <p>Stored procedure to update above View and use that view's info:</p>
  
  <pre><code>create procedure createviewupdatepenaltypointsconsecutive
      @WeekNumber nvarchar(255)
  as
  begin
      update consecutive
      as
          WITH cte as (
            SELECT *, 
              LAG([pointsRewarded], 1) OVER (PARTITION BY [EmployeeID] ORDER BY [WeekNumber]) as prev1_points,
              LAG([pointsRewarded], 2) OVER (PARTITION BY [EmployeeID] ORDER BY [WeekNumber]) as prev2_points,
              LAG([pointsRewarded], 3) OVER (PARTITION BY [EmployeeID] ORDER BY [WeekNumber]) as prev3_points
           FROM week1
  )
  SELECT *,
         CASE WHEN [pointsRewarded] = -10 AND prev1_points = -10  AND prev2_points = -10  AND prev3_points = -10 
              THEN -200
              WHEN [pointsRewarded] = -10 AND prev1_points = -10  AND prev2_points = -10 
              THEN -100
              WHEN [pointsRewarded] = -10 AND prev1_points = -10
              THEN -50
              ELSE 0
         END penalty       
  FROM cte
  
  
  /*2nd part - use info from view and add it to another table*/
  
  ;WITH cte_1
           as (SELECT EmployeeID
                     ,SUM(penalty) as totalpenalty
                FROM consecutive /*(**error says** :Msg 156, Level 15, State 1, Line 10
  Incorrect syntax near the keyword 'FROM'.) */
  
                WHERE WeekNumber = week1
                GROUP BY EmployeeID)
          UPDATE d
          SET d.Total_points_Rewarded=d.Total_points_Rewarded+c.totalpenalty,
  
          FROM cte_1 c
           JOIN EmployeeTable d on c.EmployeeID=d.EmployeeID
  end
  </code></pre>
  
  <p>Link : <a href="http://rextester.com/FSN8036" rel="nofollow noreferrer">http://rextester.com/FSN8036</a></p>
  
  <p>Can anyone help me solve the above issue to update a view and use its info in an another table.</p>