Dragon Dragon - 1 month ago 12
SQL Question

Using 3 updates in the same Store Procedure? "Small Error"

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




TT. TT.
Answer

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:

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

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;