更新视图并为其使用存储过程

I have created a view. I want to create a stored procedure to update the view and use info("penalty" column... link- http://rextester.com/GHQW83226) from view and add it to another table.

我创建了一个视图。我想创建一个存储过程来更新视图并从视图中使用info(“惩罚”列... link- http://rextester.com/GHQW83226)并将其添加到另一个表中。

View:

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

Stored procedure to update above View and use that view's info:

上面更新的存储过程查看并使用该视图的信息:

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

Link : http://rextester.com/FSN8036

链接:http://rextester.com/FSN8036

Can anyone help me solve the above issue to update a view and use its info in an another table.

任何人都可以帮我解决上述问题,以更新视图并在另一个表中使用其信息。

1 个解决方案

#1

not that place where you have indicated. but this FROM

不是你指明的地方。但是这个来自

 UPDATE d
        SET d.Total_points_Rewarded=d.Total_points_Rewarded+c.totalpenalty, -- Comma here. Remove it

        FROM cte_1 c
         JOIN EmployeeTable d on c.EmployeeID=d.EmployeeID