在同一个存储过程中使用 3 个更新?“小错误" [英] Using 3 updates in the same Store Procedure? "Small Error"

问题描述

我有以下 SP，其中有 3 个更新语句.每次执行此 SP 时，我都会收到错误消息消息 208，级别 16，状态 1，过程 sp_Rating_Comments，第 41 行无效的对象名称RatingLines".".此错误位于第二个更新语句中.当我隐藏第二个和第三个更新语句时，我的代码工作正常.知道如何将这 3 个更新语句一起使用.

I have the following SP, its has 3 update statements in it. Each I EXEC this SP i get an error "Msg 208, Level 16, State 1, Procedure sp_Rating_Comments, Line 41 Invalid object name 'RatingLines'.". This error is located in the second update statement. When I hide the second and The Third update statement my code works just fine. Any idea how can I use those 3 Update statements together.

Alter PROCEDURE [HRSDB].[sp_Rating_Comments] 
-- @BookingNr varchar(25)
-- ,@Company varchar(30)
AS
BEGIN
  SET NOCOUNT ON;
     ;WITH RatingLines AS   -- Get the important columns from both HRSDB tables
    (
	  Select   RDA.[CTS]		  AS [CTS]
			  ,RDA.[B_KEY]		  AS [B_KEY]
			  ,RDA.[H_KEY]		  AS [H_KEY]
			  ,RDA.[RT_ID]		  AS [RT_ID]
			  ,RDA.[RT_AVGRATING] AS [RT_AVGRATING]
		      ,RDDA.[RTD_COMMENT] AS [RTD_COMMENT]
		                    
		From  [DynNavHRS].[HRSDB].[HTL_RATING_ALL_DA]        RDA
		Join  [DynNavHRS].[HRSDB].[HTL_RATING_DETAIL_ALL_DA] RDDA
		ON    RDA.RT_ID =RDDA.RT_ID
	    AND   RDDA.[RTD_COMMENT] <> ''
	    AND   RDA.[B_KEY]='19214642'  -- Just to test with one rec 
    )
    
-- First Table:  
  UPDATE     [DynNavHRS].[dbo].[HRS$Agency Header]
  SET        [Booking Rating] = '1'
  FROM       [DynNavHRS].[dbo].[HRS$Agency Header] AH
  INNER JOIN RatingLines   RL1
  ON         RL1.[B_KEY] = AH.[Reservation No_]
  WHERE      RL1.[RTD_COMMENT] <> ''   -- If the Booking have a comment in DB2
  AND        [Booking Rating] = '0' ;  -- in order to avoide scanning all line
 
-- Second Table: 
  UPDATE     [DynNavHRS].[dbo].[HRS$Correction Agency Header] 
  SET        [Booking Rating] = '1'
  FROM       [DynNavHRS].[dbo].[HRS$Correction Agency Header] CL
  INNER JOIN RatingLines   RL2
  ON         RL2.[B_KEY] = CL.[Reservation No_]
  WHERE      RL2.[RTD_COMMENT] <> ''   -- If the Booking have a comment in DB2
  AND        [Booking Rating] = '0'  ; -- in order to avoide scanning all line

-- Third Table:  
  UPDATE     [DynNavHRS].[dbo].[HRS$Agency Display Line] 
  SET        [Booking Rating] = '1'
  FROM       [DynNavHRS].[dbo].[HRS$Agency Display Line] DL
  INNER JOIN RatingLines   RL3
  ON         RL3.[B_KEY] = DL.[Reservation No_]
  WHERE      RL3.[RTD_COMMENT] <> ''   -- If the Booking have a comment in DB2
  AND        [Booking Rating] = '0'   ;-- in order to avoide scanning all line

COMMIT
END

推荐答案

您使用 ;WITH RatingLines ... 启动 SP，它连接到第一个 UPDATE 语句，不是其他的.此构造创建一个仅对它后面的第一个语句可见的 CTE.更多解释可以在 TN 中找到 WITH common_table_expression (Transact-SQL).这段摘自 Remarks 的内容特别强调了这一点:

You start the SP with ;WITH RatingLines ... which connects to the first UPDATE statement, not the other ones. This construct creates a CTE that is only visible to the first statement that follows it. More explanation can be found in the TN for WITH common_table_expression (Transact-SQL). In particular this excerpt from Remarks highlights this:

CTE 后面必须跟一个引用部分或全部 CTE 列的 SELECT、INSERT、UPDATE、MERGE 或 DELETE 语句.

A CTE must be followed by a single SELECT, INSERT, UPDATE, MERGE, or DELETE statement that references some or all the CTE columns.

要让您的 SP 中的所有语句都知道此表，请改为为 RatingLines 创建一个表变量或临时表.

To have this table known for all statements in your SP, create a table variable or a temporary table for the RatingLines instead.

使用临时表的大纲如下:

Outline using a temporary table would be as follows:

Select   RDA.[CTS]        AS [CTS]
              ,RDA.[B_KEY]        AS [B_KEY]
              ,RDA.[H_KEY]        AS [H_KEY]
              ,RDA.[RT_ID]        AS [RT_ID]
              ,RDA.[RT_AVGRATING] AS [RT_AVGRATING]
              ,RDDA.[RTD_COMMENT] AS [RTD_COMMENT]
INTO #RatingLines -- Create #RatingLines as temporary table
From  [DynNavHRS].[HRSDB].[HTL_RATING_ALL_DA]        RDA
Join  [DynNavHRS].[HRSDB].[HTL_RATING_DETAIL_ALL_DA] RDDA
ON    RDA.RT_ID =RDDA.RT_ID
AND   RDDA.[RTD_COMMENT] <> ''
AND   RDA.[B_KEY]='19214642';

-- Throughout the rest of the SP, use #RatingLines as your ratings table; eg:
...
INNER JOIN #RatingLines   RL1
...

-- At the end of the SP, drop the temporary table
DROP TABLE #RatingLines;

这篇关于在同一个存储过程中使用 3 个更新?“小错误"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！