How to rollback or commit a transaction in SQL Server

Question

In my stored procedure, I have three insert statements. On duplicate key value insertion first two queries generate the error Violation of PRIMARY KEY constraint and third query runs as usual. Now I want that if any query generates any exception, everything should get rolled back. If there isn't any exception generate by any query, it should get committed. ``` declare @QuantitySelected as char set @QuantitySelected = 2 declare @sqlHeader as varchar(1000) declare @sqlTotals as varchar(1000) declare @sqlLine as varchar(1000) select @sqlHeader = 'Insert into tblKP_EstimateHeader ' select @sqlHeader = @sqlHeader + '(CompanyID,CompanyName,ProjectName,EstimateID,EstimateHeader,QuoteDate,ValidUntil,RFQNum,Revision,Contact,Status,NumConfigurations) ' select @sqlHeader = @sqlHeader + ' select CompanyID,CompanyName,ProjectName,EstimateID,EstimateHeader,QuoteDate,ValidUntil,RFQNum,Revision,Contact,Status,NumConfigurations ' select @sqlHeader = @sqlHeader + 'from V_EW_Estimate_Header where Estima…

Accepted Answer

The good news is a transaction in SQL Server can span multiple batches (each `exec` is treated as a separate batch.)

You can wrap your `EXEC` statements in a `BEGIN TRANSACTION` and `COMMIT` but you'll need to go a step further and rollback if any errors occur.

Ideally you'd want something like this:

```
BEGIN TRY
    BEGIN TRANSACTION 
        exec( @sqlHeader)
        exec(@sqlTotals)
        exec(@sqlLine)
    COMMIT
END TRY
BEGIN CATCH

IF @@TRANCOUNT > 0
        ROLLBACK
END CATCH
```

The `BEGIN TRANSACTION` and `COMMIT` I believe you are already familiar with. The `BEGIN TRY` and `BEGIN CATCH` blocks are basically there to catch and handle any errors that occur. If any of your `EXEC` statements raise an error, the code execution will jump to the `CATCH` block.

Your existing SQL building code should be outside the transaction (above) as you always want to keep your transactions as short as possible.

How to rollback or commit a transaction in SQL Server

Solution

Problem