How to roll back or commit a transaction in SQL Server

Question

In my stored procedure, I have three insert statements.

When re-entering key values, the first two queries generate an error

PRIMARY KEY constraint violation

and the third request is executed as usual.

Now I want that if any request throws an exception, everything should be thrown back.

If any exception is not thrown by any request, it should be fixed.

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 EstimateID = 2203' select @sqlTotals = 'Insert into tblKP_Estimate_Configuration_Totals ' select @sqlTotals = @sqlTotals + '(ConfigRecId,RecId,SellQty,ConfigNum,ConfigDesc,SortOrder,OptionsInMainPrice,MarkupPctQty,' select @sqlTotals = @sqlTotals + ' SellPriceQty,RubberStamp,OptPriceQty,StatusRecid,LastUpdate_Date,LastUpdate_User,TotalCost,QuantityBracketSelected)' select @sqlTotals = @sqlTotals + ' select ConfigRecId,RecId,SellQty' + @QuantitySelected + ',ConfigNum,ConfigDesc,SortOrder,OptionsInMainPrice' select @sqlTotals = @sqlTotals + ' ,MarkupPctQty' + @QuantitySelected + ',SellPriceQty' + @QuantitySelected + ',RubberStamp,OptPriceQty' + @QuantitySelected + ',StatusRecid,LastUpdate_Date,LastUpdate_User,TotalCost' + @QuantitySelected + ',' + @QuantitySelected select @sqlTotals = @sqlTotals + ' from v_EW_Estimate_Configuration_Totals where ConfigRecId = -3' select @sqlLine = 'Insert into tblKP_Estimate_Configuration_Lines' select @sqlLine = @sqlLine + '(MstrRfqRecId,RfqRecId,RfqLineRecId,CompanyId,VendorQuoteNum,LineGrp,LineNum,StatusRecId,' select @sqlLine = @sqlLine + ' LineDesc,LineSize,LineMatl,LineDeco,LineFinish,CopyFromRecId,PerPieceCost,IsOptional,' select @sqlLine = @sqlLine + ' CopyToNewRev,RecId,UnitPrice,LineQty,LinePrice,CustOrVend,SellQty1,RfqNum,ConfigLineIsOptional,ConfigLinePerPieceCost,ConfigLineRecid,SellPrice,SaleQty)' select @sqlLine = @sqlLine + ' select distinct MstrRfqRecId,RfqRecId,RfqLineRecId,CompanyId,VendorQuoteNum,LineGrp,LineNum,' select @sqlLine = @sqlLine + ' StatusRecId,LineDesc,LineSize,LineMatl,LineDeco,LineFinish,CopyFromRecId,PerPieceCost,IsOptional,' select @sqlLine = @sqlLine + ' CopyToNewRev,RecId,UnitPrice' + @QuantitySelected + ',LineQty' + @QuantitySelected + ', isnull(LinePrice' + @QuantitySelected + ', 0.0000),CustOrVend,SellQty' + @QuantitySelected + ',RfqNum,ConfigLineIsOptional,ConfigLinePerPieceCost,ConfigLineRecid,SellPrice' + @QuantitySelected + ',SaleQty' + @QuantitySelected select @sqlLine = @sqlLine + ' from v_EW_EstimateLine where rfqlinerecid in (select RfqLineRecID from kp_tblVendorRfqConfigLine where ConfigRecID = -3) ' exec( @sqlHeader) exec(@sqlTotals) exec(@sqlLine) 

Answer 1

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

You can wrap your exec statements in BEGIN TRANSACTION and COMMIT , but you will need to take another step and roll back if any errors occur.

Ideally, you need something like this:

 BEGIN TRY BEGIN TRANSACTION exec( @sqlHeader) exec(@sqlTotals) exec(@sqlLine) COMMIT END TRY BEGIN CATCH IF @@TRANCOUNT > 0 ROLLBACK END CATCH 

BEGIN TRANSACTION and COMMIT I think you are already familiar. The BEGIN TRY and BEGIN CATCH blocks basically exist to catch and handle any errors that occur. If any of your exec statements causes an error, code execution proceeds to the CATCH block.

The existing building SQL code must be outside the transaction (see above), since you always want your transactions to be as short as possible.