Sql-server – Don’t use a transaction for Stored Procedure

sql serverstored-procedures

I have a stored procedure that runs a few commands. I don't want these commands to be wrapped in the transaction of the stored procedure. If the 4th command fails, I want the 1st, 2nd and 3rd ones to stay and not rollback.

Is it possible to write the stored procedure in such a way that it doesn't all execute as one big transaction?

Best Answer

I think there may be some confusion here about a batch vs a transaction.

A transaction is a statement or set of statements that will either succeed or fail as a unit. All DDL statements are in transactions themselves (i.e. if you update 100 rows but row 98 throws an error, none of the rows are updated). You can wrap a series of statements in a transaction as well using BEGIN TRANSACTION and then either COMMIT or ROLLBACK.

A batch is a series of statements that are executed together. A stored procedure is an example of a batch. In a stored procedure, if one statement fails and there is error trapping (normally TRY/CATCH blocks) then the subsequent statements will not execute.

I suspect your issue is the batch is getting cancelled when an error occurs because either the stored proc itself or an outer scope (like the application or stored proc that calls this procedure) has error trapping in it. If that is the case this is trickier to resolve since you need to adjust how you handle errors at whatever scope is trapping them.

Related Question