問題描述

當(dāng)我嘗試驗(yàn)證以下內(nèi)容時(shí)，我遇到了一個(gè) proc 問題:

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

• @invoicenumber + @invoiceid 連接在一起使總發(fā)票號碼

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

如果數(shù)據(jù)庫中已存在總發(fā)票編號，則拋出發(fā)票
號碼已存在'.

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

我遇到的問題是，即使發(fā)票編號不存在，它仍然會(huì)引發(fā)此錯(cuò)誤.我想是因?yàn)樗呀?jīng)插入了數(shù)據(jù)，但插入后又回滾了，所以雖然看起來沒有插入表中，但它可能已經(jīng)插入了，或者我是這么認(rèn)為的.

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.

我的問題是有沒有辦法重寫這個(gè)過程以使其正常工作?也許先執(zhí)行 SELECT 并進(jìn)行驗(yàn)證檢查，如果沒問題，然后開始在事務(wù)中執(zhí)行插入?

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?

被這個(gè)問題困住了一段時(shí)間，想看看是否有更有效的方法可以避免這個(gè)問題?

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

<小時(shí)>

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

這篇關(guān)于重寫過程以避免插入然后回滾的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！