Stored procedure to update multiple tables

Question

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?

score 1 · Accepted Answer · answered Jul 12 '12 at 04:21

1

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.

answered Jul 12 '12 at 04:21

Icarus

63,293
14
100
115

In other words, if I split things up, then it is possible that the first table update goes through, but the second fails. Is there a way to roll back the first update if the second one fails if they are in separate procedures? – hgcrpd Jul 12 '12 at 04:46
@hgcrpd It's certainly possible to rollback updates within nested stored procs. Check this for a template on how to accomplish it: http://stackoverflow.com/a/2074139/345490. It's very simple: all procs must follow the same template; each proc rollsback on error and re-reaises (see the `raiserror` line inside the catch block) the error to notify the caller proc that an error has occurred so that the caller also rollsback on error. – Icarus Jul 12 '12 at 14:07

score 1 · Answer 2 · edited Jul 12 '12 at 05:16

1

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

edited Jul 12 '12 at 05:16

marc_s

732,580
175
1,330
1,459

answered Jul 12 '12 at 04:31

Vikram Jain

5,498
1
19
31

3

Welcome to StackOverflow: if you post code, XML or data samples, **please** highlight those lines in the text editor and click on the "code samples" button ( `{ }` ) on the editor toolbar to nicely format and syntax highlight it! Then you don't need all those ugly `
` tags, either! – marc_s Jul 12 '12 at 05:15

score 0 · Answer 3 · answered Jul 12 '12 at 04:25

0

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.

answered Jul 12 '12 at 04:25

Raj Jayaswal

468
1
9
22

score 0 · Answer 4 · answered Feb 27 '19 at 08:11

0

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

answered Feb 27 '19 at 08:11

Immortal

1,133
1
15
35

and how would you do that? Can you post an example? – Marcello Miorelli Oct 05 '19 at 10:12

Stored procedure to update multiple tables

4 Answers4

Linked