How update multiple tables in a Stored Procedure

Question

The following procedure will update a table called 'Process.Queue'. Can someone help modify the procedure such that it updates more than one table. For example, I would the procedure updated both tables,Process.Queue, and Process.ThroughPut

CREATE PROCEDURE Process.FinaliseProcess (@ProcessID int)
AS
BEGIN
    DECLARE @StatusID int = (SELECT ProcessStatusID FROM Process.ProcessStatus WHERE StatusName = 'Succeeded')

    UPDATE Process.Queue
    SET ProcessStatusID = @StatusID,
        ProcessEndDate = getdate()
    WHERE ProcessID = @ProcessID
END
GO

Answer 1

Hi carlton

To modify the procedure to update more than one table (Process.Queue and Process.ThroughPut), you can simply add additional UPDATE statements for each table you want to update. Here's the modified procedure:

CREATE PROCEDURE Process.FinaliseProcess (@ProcessID int) AS 
BEGIN     
   DECLARE @StatusID int = (SELECT ProcessStatusID FROM Process.ProcessStatus WHERE StatusName = 'Succeeded')      

   -- Update Process.Queue     
   UPDATE Process.Queue     
   SET   ProcessStatusID = @StatusID,         
         ProcessEndDate = GETDATE()     
   WHERE ProcessID = @ProcessID;      

  -- Update Process.ThroughPut (assuming it has the same columns as Process.Queue)     
  UPDATE Process.ThroughPut     
  SET   ProcessStatusID = @StatusID,         
        ProcessEndDate = GETDATE()     
  WHERE ProcessID = @ProcessID; 
END
GO 

try this

In this example, I assumed that the Process.ThroughPut table has the same columns as the Process.Queue table. If the tables have different column structures, you will need to adjust the UPDATE statements accordingly, ensuring that you are updating the correct columns in each table.

Answer 2

such that it updates more than one table.

One UPDATE statement can only update one table. If you want to update more tables, then you have to write a seperate UPDATE command for each table.