CODEWITHSUNDEEP
sqlsql-servert-sql
Coder
Coder

Reputation: 135

How to check something before a /

I have an exec proc below where it looks for the invoice number, id and document type:

exec SupportAudit.BI.CreateMCCInvoiceReversal 'APCCI/000', 29923, 'APCCI'

Now the document type (APCCI) as an example will always be part of the invoice number before the / (as an example APCCI).

My question is simply how to code a check to state that if anything before the '/' in @invoiceNumber does not equal @DocType, then throw an exception?

Below is the proc itself:

ALTER Procedure [BI].[CreateMCCInvoiceReversal]
(

    @InvoiceNumber      varchar(255),
    @InvoiceId          int,
    @DocType            varchar(15)

)
AS
BEGIN

SET NOCOUNT ON;
SET XACT_ABORT ON;

declare @errormsg           varchar(250);
declare @OutputList         [Core].[RollbackOutputList];
declare @CountRows          int;

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 @InvoiceNumber  +  cast(InvoiceID as varchar),
                @DocType
            FROM Jet2Fees.Discount.Invoice
            WHERE InvoiceId = @InvoiceId

Upvotes: 1

Views: 36

Answers (2)

valex
valex

Reputation: 24144

IF (@invoiceNumber NOT LIKE  @DocType +'/%') RAISERROR('Error Message',1,1);

Upvotes: 1

Unnikrishnan R
Unnikrishnan R

Reputation: 5031

Add a matching criteria before the insert statement..

IF ( SUBSTRING(@InvoiceNumber,1,CHARINDEX('/',@InvoiceNumber)-1)) != @DocType

SELECT 'ERROR : Not Matching'

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 @InvoiceNumber  +  cast(InvoiceID as varchar),
                @DocType
            FROM Jet2Fees.Discount.Invoice
            WHERE InvoiceId = @InvoiceId
END

Upvotes: 0

Related Questions