在存储过程中更改视图 [英] Alter View within stored procedure

问题描述

我们如何在存储过程中改变视图?

How can we alter view within a stored procedure ?

create procedure createviewupdatepenaltypointsconsecutive
as
begin
alter 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

end

M 收到此错误:消息 156，级别 15，状态 1，过程 createviewupdatepenaltypointsconsecutive，第 4 行关键字VIEW"附近的语法不正确.消息 319，级别 15，状态 1，过程 createviewupdatepenaltypointsconsecutive，第 8 行关键字with"附近的语法不正确.如果此语句是公用表表达式、xmlnamespaces 子句或更改跟踪上下文子句，则前一条语句必须以分号终止.

M getting this error : Msg 156, Level 15, State 1, Procedure createviewupdatepenaltypointsconsecutive, Line 4 Incorrect syntax near the keyword 'VIEW'. Msg 319, Level 15, State 1, Procedure createviewupdatepenaltypointsconsecutive, Line 8 Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

推荐答案

您将无法像在存储过程中那样运行 ALTER VIEW 语句.因此，要解决您的问题，您必须采取 2 项措施:

You will not be able to run the ALTER VIEW statement like that in your stored procedure. So to solve your problem you must take 2 actions:

1) 要纠正当前出现的错误，您必须以这样的分号开头 CTE:

1) To correct the error that currently appears you have to begin the CTE with a semicolon like that:

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

(或者甚至更好地开始使用分号终止所有 SQL 语句，因为替代方案已被弃用).

(Or even better begin terminating all your SQL Statements with semicolon as the alternative is deprecated).

2) 将您的 alter view 语句转换为动态 SQL 字符串并使用 sp_executesql 执行它，因为 ALTER VIEW 语句必须是批处理中的第一个:

2) Convert your alter view statement in a dynamic SQL string and execute it using sp_executesql as the ALTER VIEW statement must be the first one in the batch:

CREATE PROCEDURE createviewupdatepenaltypointsconsecutive
AS
BEGIN
   DECLARE @STMT AS NVARCHAR(MAX) =
   '
  ALTER 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
   '
   EXEC sp_executesql @STMT;
END

这篇关于在存储过程中更改视图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！