transacciones - try catch transaction sql server

Cómo deshacer o confirmar una transacción en SQL Server

Question

En mi procedimiento almacenado, tengo tres instrucciones de inserción.

En la inserción de valores de clave duplicados, primero dos consultas generan el error

Violación de la restricción PRIMARY KEY

y la tercera consulta se ejecuta como de costumbre.

Ahora quiero que si alguna consulta genera alguna excepción, todo debería ser retrotraído.

Si no hay ninguna excepción generada por una consulta, debería comprometerse.

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

La buena noticia es que una transacción en SQL Server puede abarcar varios lotes (cada exec se trata como un lote separado).

Puede ajustar sus declaraciones EXEC en BEGIN TRANSACTION y COMMIT pero deberá ir un paso más allá y retroceder si se produce algún error.

Lo ideal es que quieras algo como esto:

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

    IF @@TRANCOUNT > 0
        ROLLBACK
END CATCH

BEGIN TRANSACTION y COMMIT Creo que ya está familiarizado con. Los bloques BEGIN TRY y BEGIN CATCH están básicamente ahí para detectar y manejar cualquier error que ocurra. Si alguna de sus declaraciones EXEC genera un error, la ejecución del código saltará al bloque CATCH .

Su código de construcción SQL existente debe estar fuera de la transacción (arriba) ya que siempre desea mantener sus transacciones lo más cortas posible.