SQL Interview: 103 Stored procedures and transactions

SQL Interview: 103 Stored procedures and transactions

This is a post in the SQL Interview series. These aren’t trick or gotcha questions, they’re just questions designed to scope out a candidate’s knowledge around SQL Server and Azure SQL Database.

Section: Development Level: Medium

Question:

Does a stored procedure automatically provide a transaction i.e., if a statement within a stored procedure fails, are all previous data modification statements rolled back?

Answer:

No, a stored procedure does not automatically provide an implicit transaction.

If one statement fails inside a stored procedure, previous data modifications are not automatically rolled back unless you explicitly control that behavior.

By default, when you run a stored procedure:

  • Each DML statement (e.g., INSERT, UPDATE, DELETE) is its own mini-transaction.
  • If one statement fails (for example, due to a constraint violation or conversion error), that statement is rolled back — but earlier successful statements remain committed.

2025-12-30