Coder Coder - 3 months ago 24
SQL Question

Rewriting a proc to avoid an insert then rollback

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


  • @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.

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?

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

Answer

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