如何在 SQLServer 中回滚或提交事务

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

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

违反主键约束

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

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

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

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)
296024 次浏览
小开
最佳答案

好消息是 SQLServer 中的事务可以跨多个批处理(每个 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 构建代码应该位于事务之外(上面) ，因为您总是希望尽可能缩短事务。


提交答案