Menu
If I create a Stored Procedure in SQL and call it (EXEC spStoredProcedure) within the BEGIN/END TRANSACTION, does this other stored procedure also fall into the transaction?
I didn't know if it worked like try/catches in C#.
439
Yes, all nested stored procedure calls are included in the scope of the transaction.
Remember that if a BEGIN statement starts a transaction block before calling a stored procedure, the stored procedure always returns with an active transaction block, and you must issue a COMMIT or ROLLBACK statement to complete or abort that transaction.
END at the start and end of a stored procedure and function. But it is not strictly necessary. However, the BEGIN... END is required for the IF ELSE statements, WHILE statements, etc., where you need to wrap multiple statements.
Expand the database that you want, expand Programmability, and then expand Stored Procedures. Right-click the user-defined stored procedure that you want and select Execute Stored Procedure. In the Execute Procedure dialog box, specify a value for each parameter and whether it should pass a null value.
Yes, everything that you do between the Begin Transaction and Commit (or Rollback) is part of the transaction.
170
Sounds great, thanks a bunch. I ended up doing something like this (because I'm on 05)
BEGIN TRY BEGIN TRANSACTION DO SOMETHING COMMIT END TRY BEGIN CATCH IF @@TRANCOUNT > 0 ROLLBACK -- Raise an error with the details of the exception DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int SELECT @ErrMsg = ERROR_MESSAGE(), @ErrSeverity = ERROR_SEVERITY() RAISERROR(@ErrMsg, @ErrSeverity, 1) END CATCH If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With