CODEWITHSUNDEEP
sqlsql-serverstored-procedures
hgcrpd
hgcrpd

Reputation: 1900

Stored procedure to update multiple tables

I have a stored procedure that updates two tables. The first table is always the same table, but the second table changes depending on a parameter that is passed in. Is it more efficient to write it all into one big procudure, as such

CREATE PROCEDURE MyBigProc
    @id int
    @param int,
    @value1 int,
    @value2 int
AS
BEGIN
    SET NOCOUNT ON;

    -- First table to update
    UPDATE MyTable SET field1 = @value1 WHERE id = @id

    -- Then choose which table to update based on @param
    IF @param = 1
       UPDATE MySecondTable SET field2 = @value2 WHERE id = @id

    IF @param = 2
       UPDATE MyThirdTable SET field2 = @value2 WHERE id = @id
END

Or should I write a separate procedure for each table and then call EXEC the procedure from the main procedure.

I suppose the latter is more flexible, say if I wanted to update a subtable but no the main table?

Upvotes: 3

Views: 24037

Answers (4)

Immortal
Immortal

Reputation: 1180

Best option is to use a CASE statement to update your tables

Upvotes: 0

Vikram Jain
Vikram Jain

Reputation: 5588

CREATE PROCEDURE MyBigProc 
    @id int,
    @param int,
    @value1 int,
    @value2 int
AS
BEGIN
    SET NOCOUNT ON;

    -- First table to update
    UPDATE MyTable SET field1 = @value1 WHERE id = @id;

    -- Then choose which table to update based on @param
    IF @param = 1     
        exec SP_MySecondTable @id,@value2;
    IF @param = 2
       exec SP_MyThirdTable @id,@value2;
END

CREATE PROCEDURE SP_MySecondTable
    @id int, 
    @value2 int
AS
BEGIN  
    UPDATE MySecondTable SET field2 = @value2 WHERE id = @id;
END

CREATE PROCEDURE SP_MyThirdTable
    @id int, 
    @value2 int
AS
BEGIN  
  UPDATE MyThirdTable SET field2 = @value2 WHERE id = @id;
END

Upvotes: 1

Raj Jayaswal
Raj Jayaswal

Reputation: 478

Its better to have different stored procedures and then call them all at a single place. It'll help you a lot while performing maintenance activities.

Upvotes: 0

Icarus
Icarus

Reputation: 63966

I suppose the latter is more flexible, say if I wanted to update a subtable but no the main table?

Exactly, you have a good reason to split the work on 2 separate procs. If it makes sense for you for everything else, I don't see why not follow that approach.

One possible reason not to do it, would be if you need both updates to succeed or fail at the same time. Under a scenario like this, I would leave everything in one proc and enclose everything in one transaction.

Upvotes: 1

Related Questions