Set next highest number in nvchar-field 2

[Pampers]

Hi Everyone,
I have a table Customer with an AccountNumber field (nvchar20). The user can set this 'number' by itself. But if the user leaves it blank I want Sql server fill it up with the next highest numeric value in the column.

AccountNumber (nvarchar(20)
--------------
000001
ABC9123
000002
TEST789
......

On the dots (empty AccountNumber), I want 000003 to appear. How do I go about this?




Pampers
Keeping it simple can be complicated

 

[bborissov]

DECLARE @Temp TABLE (AccountNumber nvarchar(20))
INSERT INTO @Temp VALUES('000001')
INSERT INTO @Temp VALUES('ABC9123')
INSERT INTO @Temp VALUES('000002')
INSERT INTO @Temp VALUES('TEST789')

SELECT CONVERT(nvarchar(20),RIGHT('000000'+CAST(CAST(MAX(AccountNumber) as int)+1 as varchar(20)),6))
FROM @Temp
WHERE ISNUMERIC(AccountNumber) = 1

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[Pampers]

Tnx a lot bborissov,
Works like a charm.

Pampers
Keeping it simple can be complicated

 

[bborissov]

I just hope you didn't have such AccountNumbers:
10E2
10D2
because the code above will bombs.
10E2 or 10D2 are considered numeric and ISNUMERIC() returns 1 for them.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[ESquared]

then use

WHERE LTrim(RTrim(AccountNumber)) NOT LIKE '%[^0-9]%'

to get only rows that have nothing but digits

 

[Pampers]

Hi bborissov and ESquared ,
No I don't have those values (yet). But since the user can enter is own made accountnumbers, in the future it might. So I will put Esquared Trimming in to make sure the accountnumber holds 'real' digits. Tnx.

Pampers
Keeping it simple can be complicated

 

[maswien]

how about multiple users update the same database?

 

[ESquared]

Yes that is always an issue. My instinct is to avoid letting people create their own account numbers, but if that is an absolute requirement AND if a separately formed set of sequential account numbers are needed, I see two requirements:

1 - Customer can enter any account number manually, except for one that is in the exact format of the sequential series. So for example, if the sequential series is all numbers and is 6 digits long, then the manually entered number must either contain a letter or be 5 digits or shorter. This avoids any collision with automatically-generated numbers.
2 - A table with one row (or one row per automatically generated series, perhaps there could be different schemes involving numbers and letters) that always has the next available account number in it. Then, a stored procedure that returns a number for use:

create table #nextaccountnumber (a int)
insert #nextaccountnumber select 1 -- first available number is 1
GO
create procedure #getnextnumber @nextaccountnumber int output
as
set nocount on
declare @i int
update #nextaccountnumber set a = a + 1, @nextaccountnumber = a
GO

-- now get the next account number available
declare @num int
exec #getnextnumber @num output
print @num

There is another option which is for the customer to choose an option "<select account number for me>" and then only get the max() value at record insertion time, with appropriate locks to guarantee no one else collides with that number who happens to submit the request at just the right time creating a race condition. But the drawback is that the user won't see the new account number until after the row is inserted.

The drawback of the above method is that numbers get consumed whether or not the user finally presses save to create the account, so some numbers will be left unused for actual accounts.

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.

_{The best part about anything that has cheese is the cheese.}[/color]

 

[Pampers]

Hi there,

What I'm building is an add-on onto on existing program where there is the option of a user setting a CustomerAccountNo, and when leaving it blank, he/she gets the option to let the system generate a (sequential) number. So ESquared, I think the 3th option you describe is what the existing program offers. In my add-on (where the user also gets an option to create a customer) I will try to provide (more or less) for the same.



Pampers
Keeping it simple can be complicated