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

Solution 1:

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