如何在 SQL Server 中回滚或提交事务 [英] How to rollback or commit a transaction in SQL Server

问题描述

在我的存储过程中，我有三个插入语句.

插入重复键值时，前两个查询产生错误

<块引用>

违反 PRIMARY KEY 约束

第三个查询照常运行.

现在我希望如果任何查询产生任何异常，一切都应该回滚.

如果任何查询都没有产生任何异常，它应该被提交.

声明@QuantitySelected 为字符设置@QuantitySelected = 2将@sqlHeader 声明为 varchar(1000)将@sqlTotals 声明为 varchar(1000)将@sqlLine 声明为 varchar(1000)select @sqlHeader = '插入到 tblKP_EstimateHeader '选择@sqlHeader = @sqlHeader + '(CompanyID,CompanyName,ProjectName,EstimateID,EstimateHeader,QuoteDate,ValidUntil,RFQNum,Revision,Contact,Status,NumConfigurations)'select @sqlHeader = @sqlHeader + '选择 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 = '插入到 tblKP_Estimate_Configuration_Totals '选择@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'+ @QuantitySelectedselect @sqlTotals = @sqlTotals + ' from v_EW_Estimate_Configuration_Totals where ConfigRecId = -3'select @sqlLine = '插入到 tblKP_Estimate_Configuration_Lines'选择@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 + ' 选择不同的 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' + @QuantitySelectedselect @sqlLine = @sqlLine + ' from v_EW_EstimateLine where rfqlinerecid in (select RfqLineRecID from kp_tblVendorRfqConfigLine where ConfigRecID = -3) '执行(@sqlHeader)exec(@sqlTotals)执行(@sqlLine)

解决方案

好消息是 SQL Server 中的事务可以跨越多个批处理(每个 exec 被视为一个单独的批处理.)

您可以将 EXEC 语句包装在 BEGIN TRANSACTION 和 COMMIT 中，但您需要更进一步并回滚(如果有)发生错误.

理想情况下，您会想要这样的东西:

开始尝试开始交易执行(@sqlHeader)exec(@sqlTotals)执行(@sqlLine)犯罪结束尝试开始捕捉如果@@TRANCOUNT >0回滚收尾

BEGIN TRANSACTION 和 COMMIT 相信你已经很熟悉了.BEGIN TRY 和 BEGIN CATCH 块基本上用于捕获和处理发生的任何错误.如果您的任何 EXEC 语句引发错误，代码执行将跳转到 CATCH 块.

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

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

解决方案

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.

这篇关于如何在 SQL Server 中回滚或提交事务的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！