Menu
Say I have a stored procedure consisting of several separate SELECT, INSERT, UPDATE and DELETE statements. There is no explicit BEGIN TRANS / COMMIT TRANS / ROLLBACK TRANS logic.
How will SQL Server handle this stored procedure transaction-wise? Will there be an implicit connection for each statement? Or will there be one transaction for the stored procedure?
Also, how could I have found this out on my own using T-SQL and / or SQL Server Management Studio?
Thanks!
824
Yes, a stored procedure can be run inside a transaction.
A stored procedure is a prepared SQL code that you can save, so the code can be reused over and over again. So if you have an SQL query that you write over and over again, save it as a stored procedure, and then just call it to execute it.
SQL prepared statements ( PREPARE , EXECUTE , DEALLOCATE PREPARE ) can be used in stored procedures, but not stored functions or triggers. Thus, stored functions and triggers cannot use dynamic SQL (where you construct statements as strings and then execute them).
There will only be one connection, it is what is used to run the procedure, no matter how many SQL commands within the stored procedure.
since you have no explicit BEGIN TRANSACTION in the stored procedure, each statement will run on its own with no ability to rollback any changes if there is any error.
However, if you before you call the stored procedure you issue a BEGIN TRANSACTION, then all statements are grouped within a transaction and can either be COMMITted or ROLLBACKed following stored procedure execution.
From within the stored procedure, you can determine if you are running within a transaction by checking the value of the system variable @@TRANCOUNT (Transact-SQL). A zero means there is no transaction, anything else shows how many nested level of transactions you are in. Depending on your sql server version you could use XACT_STATE (Transact-SQL) too.
If you do the following:
BEGIN TRANSACTION EXEC my_stored_procedure_with_5_statements_inside @Parma1 COMMIT everything within the procedure is covered by the transaction, all 6 statements (the EXEC is a statement covered by the transaction, 1+5=6). If you do this:
BEGIN TRANSACTION EXEC my_stored_procedure_with_5_statements_inside @Parma1 EXEC my_stored_procedure_with_5_statements_inside @Parma1 COMMIT everything within the two procedure calls are covered by the transaction, all 12 statements (the 2 EXECs are both statement covered by the transaction, 1+5+1+5=12).
78
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
