Tutorial :Multiple update statements in one StoredProcedure


I am wondering if it is possible to have multiple Update statements in a store procedure

Something like this:

Update Table1 set field1 = @new_value where id = @table1_id    Update Table2 set field2 = @new_value where id = @table2_id    Update Table3 set field3 = @new_value where id = @table3_id  

Right now I am executing them seperately but as they are only used together I wonder if they could be located in just one SP.


Yes, it's possible:

CREATE PROCEDURE prc_update (@table1_id INT, @table2_id INT, @table3_id INT, @new_value INT)  AS  BEGIN          UPDATE  Table1          SET     field1 = @new_value          WHERE   id = @table1_id            UPDATE  Table2          SET     field2 = @new_value          WHERE   id = @table2_id            UPDATE  Table3          SET     field3 = @new_value          WHERE   id = @table3_id  END  


Yes, that works fine.

Also put this in the stored procedure before the updates:

set nocount on  

This keeps the stored procedures from creating result sets for queries without a result. Otherwise each update will produce an empty result set that is sent back to the client.


You should wrap those statments in transactions as well so that if one fails all are rolled back.

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Next Post »