Multiple update statements in one StoredProcedure
Yes, it's possible:
CREATE PROCEDURE prc_update (@table1_id INT, @table2_id INT, @table3_id INT, @new_value INT)ASBEGIN 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_idEND
Yes, that works fine.
Also put this in the stored procedure before the updates:
set nocount onThis 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.