如何在 SQL Server 中回滚或提交事务

How to rollback or commit a transaction in SQL Server

提问by Code Rider

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

违反 PRIMARY KEY 约束

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

回答by Code Magician

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

好消息是 SQL Server 中的事务可以跨越多个批次（每个批次都exec被视为一个单独的批次。）

You can wrap your EXECstatements in a BEGIN TRANSACTIONand COMMITbut you'll need to go a step further and rollback if any errors occur.

您可以将EXEC语句包装在 a 中BEGIN TRANSACTION，COMMIT但是如果发生任何错误，您需要更进一步并回滚。

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 TRANSACTIONand COMMITI believe you are already familiar with. The BEGIN TRYand BEGIN CATCHblocks are basically there to catch and handle any errors that occur. If any of your EXECstatements raise an error, the code execution will jump to the CATCHblock.

在BEGIN TRANSACTION和COMMIT我相信你已经熟悉了。在BEGIN TRY和BEGIN CATCH块基本上没有捕获并处理出现的任何错误。如果您的任何EXEC语句引发错误，代码执行将跳转到该CATCH块。

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

您现有的 SQL 构建代码应该在事务之外（上图），因为您总是希望使您的事务尽可能短。