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

Question

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

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

“主键约束冲突”

第三个查询会像往常一样运行。

现在，如果任何查询生成任何异常，我希望所有内容都能回滚。

如果没有任何查询生成异常，则应提交。

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)

- Code Rider

---

欢迎来到StackOverflow：如果您发布代码、XML或数据样本，请在文本编辑器中突出显示这些行，并单击编辑器工具栏上的“代码示例”按钮（{}）以使其格式化和语法高亮！ - marc_s

@marc_s 谢谢Marc。我已经添加了我的存储过程，请查看一下。 - Code Rider

这是一个存储过程 - 一种存储在SQL Server内部的过程 - 它与“商店”无关.... - marc_s

实际上，这里我根据一些条件将数据从一张表保存到另一张表。这是我存储过程的主要扩展。 - Code Rider

Answer 1

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

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

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

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

    IF @@TRANCOUNT > 0
        ROLLBACK
END CATCH

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

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

- Code Magician

---

当我执行该语句时，它运行得很好。但是在存储过程中，第一次运行正常，第二次执行存储过程时，它会生成异常消息：“执行后的事务计数表明有不匹配的BEGIN和COMMIT语句。之前的计数=0，当前计数=1。” - Code Rider

抱歉，应该是“IF @@TRANCOUNT > 0”，我会更新我的答案。 - Code Magician