How to update multiple tables with a stored procedure?
Firstly, you should probably consider doing a little research first. Code Project has a lot of great articles on the subject. Secondly, whenever you try and update multiple tables with a single stored procedure, you should do it in 1 transaction and if there are any problems, then you can rollback the tables to their before state. Below is a generic stored procedure that updates multiple tables and if there is a problem, will rollback the stored procedure so your data doesn't get corrupted.
CREATE PROCEDURE YourProc
(
@TableID INT
)
AS
BEGIN
BEGIN TRY
BEGIN TRANSACTION;
UPDATE Table1
SET SalesLastYear = SalesLastYear + @SalesAmt
WHERE Table1ID = @TableID;
UPDATE Table2
SET SalesLastYear = SalesLastYear + @SalesAmt
WHERE Table2ID = @TableID;
UPDATE Table3
SET SalesLastYear = SalesLastYear + @SalesAmt
WHERE Table3ID = @TableID;
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
DECLARE @ErrorNumber INT = ERROR_NUMBER();
DECLARE @ErrorLine INT = ERROR_LINE();
PRINT 'Error number: ' + CAST(@ErrorNumber AS VARCHAR(10));
PRINT 'Line number: ' + CAST(@ErrorLine AS VARCHAR(10));
THROW;
END CATCH
END;