Tutorial :Multiple update statements in one StoredProcedure



Question:

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.


Solution:1

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  


Solution:2

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.


Solution:3

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
Previous
Next Post »