Using 3 Updates in the Same Store Procedure? "Small Error"

Using 3 updates in the same Store Procedure? Small Error

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;

Temp view in stored procedure is valid only on first UPDATE

Yes Temp view will be invalid because scope of cte is limited to first update statement. So you insert results into temp table. And use it down in procedure where ever you want.

 USE [MyDB]
    GO
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER PROCEDURE [dbo].[SP_MySP]
        @passedParam VARCHAR(32)
    AS
    BEGIN
       if object_id('tempdb..#t1') is not null
        drop table #t1
            SELECT [myTable1].[Id] into #t1
            FROM [dbo].[myTable1] LEFT JOIN [dbo].[myTable2] ON [myTable1].Id = [myTable2].[DeviceId]
            WHERE 

    --The temp view is valid here
    UPDATE [dbo].[myTable1]
    SET [myTable1].[Ready] = 0, [myTable1].[Reason] = NULL
    WHERE
    [myTable1].[Id] IN (SELECT [Id] FROM #t1)

    --The temp view is NO LONGER VALID from this point
    UPDATE [dbo].[myTable1]
    SET [myTable1].[Reason] = CONCAT([myTable1].[Reason],'Reason 1.')
    WHERE
    --all kind of conditions

    UPDATE [dbo].[myTable1]
    SET [myTable1].[Reason] = CONCAT([myTable1].[Reason],'Reason 2.')
    WHERE
    --all kind of conditions

    UPDATE [dbo].[myTable1]
    SET [myTable1].[Reason] = CONCAT([myTable1].[Reason],'Reason 3.')
    WHERE
    --all kind of conditions

    UPDATE [dbo].[myTable1]
    SET [myTable1].[Reason] = CONCAT([myTable1].[Reason],'Reason 4.')
    WHERE
    --all kind of conditions

    END

UPDATE statement not working in stored procedure; No error & no exception is happening

The UPDATE statement is working, but updates 0 rows, as shown per your log (Row Updated =0). This is not an error, your updates evaluate the where clause, finds 0 rows matching, and performs 0 modification. In Oracle, an update that doesn't match any row in the where clause is still a success.

Now why does it happen. Let's take your first update:

UPDATE tblhotel
   SET acrooms = acrooms, nacrooms = nacrooms
 WHERE date1 = (to_date(date1, 'mm/dd/yyyy'))
   AND hotelid = 'hotelid' ;

I think you want to update the row that have the column hotelid with the value passed as parameter. There are a few problems with that:

• First of all, you're not comparing the column and the parameter, but the column with a constant. Parameters don't use quotes. Constants (VARCHAR2) do.
• Secondly, you shouldn't call a parameter with the same name as your column, this causes confusion and could even cause variable shadowing. I recommend using a prefix that no column whatsoever uses in your schema. One common prefix for parameters is p_.
• Finally, you don't need conversion functions if your parameter are of the good type (since your parameter p_date1 is of type date, you don't need the to_date function).

So if you rename your parameters p_hotelid and p_date1, your statement should read:

UPDATE tblhotel
   SET acrooms = acrooms, nacrooms = nacrooms
 WHERE date1 = p_date1
   AND hotelid = p_hotelid;

In this case there is no confusion nor conversion error possible.

---

On an unrelated note:

• there seems to be a mismatch between your procedure name and the log it generates: no insertion will ever be done by update statements.
• Don't catch when others, let the error propagate. PL/SQL will rollback the procedure changes if the error propagates. PL/SQL statements (DML and blocks) are atomic by nature, they either fail completely or succeed entirely.

How to make a SQL UPDATE procedure use more than 1 table in the calculation? Doing so gives me error 1054

Do you just want to refer to new? If so, I assume you only want to update one row in stock rather than all of them. You need a where clause

CREATE DEFINER=`root`@`localhost` TRIGGER reduce_available
AFTER INSERT ON book_customer FOR EACH ROW
BEGIN
    UPDATE stock s
        SET s.current_amount = s.current_amount - new.amount
        WHERE s.book_id = new.book_id;
END;

How can multiple Stored Procedures update same row at same time?

You have to admit, this seems like a highly unusual thing to do. I wonder if it wouldn't be better to update separate tables, and then have a single update statement at the end that would join the individual tables to the final one? (i.e. update a set a.[1] = ... from a inner join b inner join c etc.).

But, if you want to continue down this path, then just set READ UNCOMMITTED from within each of your stored procedures. That is the best bet.

Stored procedure inserting the same record repeatedly instead of looping through list from SELECT

You have the same problems as another recent post here: Iterate over a table with a non-int id value

1. Why do a loop? Just do it as a single SQL statement
2. If you must use a loop, you will need to update your @Dealer value at each run (e.g., to the next DealerId) otherwise it will just infinitely loop with the same DealerID value
3. Don't do a loop.

Here's an example not needing a loop.

ALTER PROCEDURE [dbo].[P_billing_batch_set]
    @varBillingBatchRosterID int
AS 
BEGIN
SET NOCOUNT ON;

BEGIN TRY

INSERT INTO dbo.billing_batch (DealerID, BillingBatchRosterID)
    SELECT DISTINCT vi.DealerID, @varBillingBatchRosterID
    FROM dbo.vehicle_info vi
        INNER JOIN dbo.dealer_info di ON di.DealerID = vi.DealerID
    WHERE di.DealerActive = 1 
        AND (vi.ItemStatusID < 4 
            OR vi.ItemStatusID = 5
            OR vi.ItemStatusID = 8
            );

END TRY
BEGIN CATCH
    SELECT  ' There was an error: '  + error_message() AS ErrorDescription;
END CATCH;

END;

Note I

• Changed the LEFT JOIN to an INNER JOIN as your WHERE clause needs the record to exist in the dealer_info table
• Moved the SET NOCOUNT ON; to be within the BEGIN-END section
• Moved the END to the end
• Renamed your stored procedure as per the excellent comment from @marc_s (on the question itself)

---

Related Topics