重写过程以避免插入然后回滚

2021-09-10 00:00:00 sql tsql sql-server

当我尝试验证以下内容时，我遇到了一个 proc 问题:

i am having an issue with a proc where when I try to validate the following:

  • @invoicenumber + @invoiceid 连接在一起使总发票号码

  • @invoicenumber + the @invoiceid concatenate together to make the overall Invoicenumber

如果数据库中已存在总发票编号，则抛出发票
号码已存在'.

if overall invoicenumber already exists in database, throw 'Invoice
Number already exists'.

我遇到的问题是，即使发票编号不存在，它仍然会引发此错误.我想是因为它已经插入了数据，但插入后又回滚了，所以虽然看起来没有插入表中，但它可能已经插入了，或者我是这么认为的.

The problem I am having is that even if the invoice number doesn't exist, it still throws this error. I think it is because it has inserted the data but is then rolledback after the insert, so though it looks like it's not inserted into the table, it may have already been inserted, or that's what I believe.

我的问题是有没有办法重写这个过程以使其正常工作?也许先执行 SELECT 并进行验证检查，如果没问题，然后开始在事务中执行插入?

My question is that is there a way to re-write this proc to get it working correctly? Maybe perform a SELECT first and do the validation checks and if that's ok then start performing the insert within the transaction?

被这个问题困住了一段时间，想看看是否有更有效的方法可以避免这个问题?

Been stuck on this issue for a while so like to see if there is a much more efficent way to see if this problem can be avoided?

下面是proc和exec:

Below is the proc and exec:

exec SupportAudit.BI.CreateMCCInvoiceReversal 'ABCD/000', 29923, 'ABC', 1

<小时>

USE [SupportAudit]
GO
/****** Object:  StoredProcedure [BI].[CreateMCCInvoiceReversal]    Script Date: 29/08/2016 07:23:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [BI].[CreateMCCInvoiceReversal]
(

    @InvoiceNumber      varchar(255),
    @InvoiceID          int,
    @DocType            varchar(15),
    @TaskLogid          int

)

AS

BEGIN

SET NOCOUNT ON;
SET XACT_ABORT ON;

declare @OutputList         [Core].[RollbackOutputList];
declare @procname           sysname; 


Set @procname = OBJECT_NAME(@@ProcID)


BEGIN TRY

BEGIN TRAN MCCInvoiceReversal

        INSERT INTO [Jet2Fees].Discount.Invoice
                (
                InvoiceNumber,
                DocType,
                )

        OUTPUT '[Jet2Fees].Discount.Invoice', 'InvoiceID', inserted.InvoiceId,
                                Core.insXMLFragment('InvoiceId')+Core.addnlXMLFragment('InvoiceId', inserted.InvoiceId)


            INTO @OutputList

            SELECT CONCAT(@InvoiceNumber, cast(InvoiceID as varchar(50))),
                    @DocType,

            FROM Jet2Fees.Discount.Invoice
            WHERE InvoiceId = @InvoiceId

    -- see if invoice number already exisits
if exists (select 1 from Jet2Fees.Discount.Invoice where InvoiceNumber = CONCAT(@InvoiceNumber, cast(@InvoiceID as varchar(50))))
            BEGIN;
                ROLLBACK TRAN MCCInvoiceReversal
                set @errormsg = 'Invoice Number already exists';
                THROW 99999, @errormsg, 1
            END;

exec Core.insertRollbackXML @outputList, @TaskLogid, @procname

COMMIT TRANSACTION MCCInvoiceReversal


END TRY

推荐答案

修改您的 TRY 块，如下所示..

Modify your TRY block like below..

BEGIN TRY


IF exists (select 1 from Jet2Fees.Discount.Invoice where InvoiceNumber = CONCAT(@InvoiceNumber, cast(@InvoiceID as varchar(50))))
            BEGIN;
                set @errormsg = 'Invoice Number already exists';
                THROW 99999, @errormsg, 1
            END
ELSE 

     BEGIN
        INSERT INTO [Jet2Fees].Discount.Invoice
                (
                InvoiceNumber,
                DocType
                )

        OUTPUT '[Jet2Fees].Discount.Invoice', 'InvoiceID', inserted.InvoiceId,
                                Core.insXMLFragment('InvoiceId')+Core.addnlXMLFragment('InvoiceId', inserted.InvoiceId)


            INTO @OutputList

            SELECT CONCAT(@InvoiceNumber, cast(InvoiceID as varchar(50))),
                    @DocType
            FROM Jet2Fees.Discount.Invoice
            WHERE InvoiceId = @InvoiceId
          END

END TRY

相关文章