Update view and use a stored procedure for it
<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>