Auto Generate BP Code

[Former Member]

Hi Guys,

I have the following code running at a client:

declare @Pad nvarchar(10)

declare @CodeLen nvarchar(10)

declare @Code nvarchar(10)

declare @FinalCode nvarchar(10)

set @Code = (select cast(max(SUBSTRING(cardcode,3,5))+1 as nvarchar(20)) from OCRD where cardtype = 'C' and CardCode like 'AB%' and frozenfor = 'n')

set @CodeLen = (select LEN(@Code))

set @Pad = (select case @CodeLen when 1 then '0000' when 2 then '000' when 3 then '00' when 4 then '0' when 5 then '' end )

set @FinalCode = 'AB'+@Pad+@Code

select @FinalCode

They have been running this for a while, basically it auto generates the next BP code in the sequence.

It was on "AA+number", however, the number eventually reached AA99999, therefore I had to go into the code and change it to "AB".

What can I add to the code, that automatically when it reaches the end of the AB sequence it will jump to AC and so on...

This code was written by another consultant, with better SQL knowledge than I.

Your help will be appreciated.

Thanks

Regards

[Former Member · Accepted solution]

Hi,

Since you have so many new BP, better increase one or two digits to accommodate your need. Otherwise, manual change in the max number would be you better choice than others.

Thanks,

Gordon

[Former Member · Accepted solution]

Thanks Gordon, good advice...

Regards

Dario