Hi,

I am using SQL 2005

I am struggling to get a sql query right and would appreciate your help.

I need to generate the next number automatically based on some values already in the db.

In the database I have two fields:

OrderNumber int and InvoiceSeed varchar

The users are allowed to enter only numbers, characters and a dot, semicolon and forward is allowed. I have taken care of separating the seed (characters allowed) from the number.

So in the OrderNumber field I will always have an integer from 0 to .........

In the InvoiceSeed I will have the characters including leading zeros.

I need to concatenate the InvoiceSeed with the next OrderNumber.

CASES

1 - If the user types 1

I insert an empty string for InvoiceSeed and 1 for OrderNumber.

Therefore, the code below will return 2

2 - If the user types 'XX'

I insert XX for InvoiceSeed and 0 (zero) for the OrderNumber field.

Therefore, the code below will return XX1

3 -If the user types XX0 I am having a problem.

I insert XX0 into the InvoiceSeed field and 0 into the OrderNumber.

The code below will return XX01

I would like the code to return XX1 but I cannot figure out how to do that in SQL.

4 - If the user enters XX09

I insert XX0 into the InvoiceSeed field and 9 into the OrderNumber.
The code below will return XX10 which is right.

Therefore, I have a problem when the user enters XX0 or XX00.

I would appreciate if you could shed some light on how to solve this problem.

Cheers

C

DECLARE @NextON asint
DECLARE @CurrentON asint
DECLARE @seed asvarchar(20)

SELECT
@CurrentON = NextOrderNumber,
@seed = InvoiceSeed
FROM
tblCompany
WHERE
CompanyID = @CompanyId

SET @NextON = @CurrentON + 1
-- from 9 to 10 will be different and last character is 0
IF(len(convert(varchar,@NextON))!=len(convert(varchar,@CurrentON)))
and substring(@seed,len(@seed),1) = '0'

BEGIN
-- remove last 0
SET @seed = substring(@seed,1,len(@seed) - 1)

END

SELECTISNULL(@seed,'')+convert(VARCHAR(200), @NextON)AS NextIONumber

Hi pallone,

May i suggest assigning identifier as numeric datatype and set with autonumbering at database platform.

My guess is, you are trying to assign a varchar to your order and invoice numbering field,

the char type can be assigned at front-end as it is only applicable for viewing and reporting purpose, thus making your life easier.

Hi fdtoo,

Thanks for the reply.