How does SQL Server treat statements inside stored procedures with respect to transactions?

Question

Hi All!

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!

Answer 1

You can find out on your own by creating a small stored procedure that does something simple, say insert a record into a test table. Then Begin Tran; run sp_test; rollback; Is the new record there? If so, then the SP ignores the outside transaction. If not, then the SP is just another statement executed inside the transaction (which I am pretty sure is the case).

Answer 2

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).