Auto-incrementing by prefix 1

[mrees]

I need to create an automated process where we can enter a clients’ 3 letter prefix eg “ABC” and then take the next available number eg 2. I can then create the unique client reference, in this case ABC002.

My problem is that I can’t think of a smart way to get the auto-increment by prefix. Some clients make more requests than others, and it is important that each clients request is incremented by 1 from the last.

Any help would be appreciated, we are using Sql 2000.

Thanks in advance

mrees

 

[nigelrivett]

Easiest way is to make this two fields prefix and sequence.

Make the sequence an identity and calculate the sequence number for presentation.

If you are doing a lot of queries and few updates then have another field for the seq and update it using a trigger on insert. You could use this trigger to concatenate with the prefix too.
If you go the trigger route it is maybe easier to have another table to hold prefix and last seq no and do the increment/concatenate from there but I would still advise holding as separate fields on the table.


======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[markVII]

In Oracle you would use a sequence object... In SQL Server, you can do a similar stuff by creating a procedure or a trigger (whatever seems more convenient) that will insert record a special table where one column is an IDENTITY column with seed 1 and increment 1. Them for each inquery make an insert into the table, and then check @@IDENTITY global function. Doing this within a transaction guarantees consistency, and you could have an additional benefit of keeping track on who and when created the ID.

 

[markVII]

ooops, I meant SCOPE_IDENTITY() function, not @@IDENTITY
And you could make the procedure a function

 

[Andel]

What you will do is to create a lookup table with a "company name" and "last used number" column. Whenever you insert something to your table, you will query this lookup table first to see the last used number and add 1 to it. and that's what you use for the incremental prefix number. After insert, you need to update this lookup table to add 1 to the last used number.

Andel
andel@barroga.net

http://andyb.8m.com

 

[mrees]

Thanks for those ideas, the most apt solution would appear to be Andels, as the others use Identity columns. I always asumed that an Identity column created a new UNIQUE number on each insert.

The way my system needs to work is for example if both nigelrivett and markVII are clients of mine, I would give them each a 3 letter prefix, so lets say NRT and MRK. They both make their first request, so they get request ID's of NRT001 and MRK001, as it is their first one. markVII makes another two requests, which become MRK002 and MRK003. nigelrivetts next request would get NRT002. I think you get the idea. So am I right in saying the an Identity column for the request number would not work?

Does anyone have any further suggestions?

Thanks

mrees

 

[nigelrivett]

No.
There is a difference between data storage and presentation.
What you have is a presentation issue. All you need is to store the data required for that presentation - for thgis an identity is fine (see my previous post).

I think Andel's suggestion was at the end of my previous post too.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[mrees]

Nigelrivett,

I think I must be missing something with your post, I fully agree with yours and Andels suggestion of holding the latest or next available request number per client.

I just can't see how an Identity column would work with this, or was this a separate issue?

cheers

mrees

 

[gazal]

hi

i have similar requirement, but can someone give me a working example???


Gazal

 

[cheyney]

/* I assume you have a PREFIX column (char 5) and a
SEQUENCE column (char 3) (and perhaps a formula column that
concatenates PREFIX + SEQUENCE, but this is irrelevant).
I am also assuming that there is an identity key column called TableID
You would insert PREFIX when you insert, and allow
this trigger to properly populate SEQUENCE
*/

CREATE TRIGGER SequenceNumber ON tblTable
FOR INSERT
AS

DECLARE @TempSeq int
DECLARE @TempPre char(3)
DECLARE @TableID int

SELECT @TempPre = PREFIX, @TableID = TableID
FROM INSERTED

SELECT @TempSeq = IsNull(Max(Cast(SEQUENCE as int)), 0) + 1
FROM tblTable
WHERE PREFIX = @TempPre

UPDATE tblTable
SET SEQUENCE = Replicate('0', 5-len(cast(@TempSeq as varchar(5)))) + cast(@TempSeq as varchar(5))
WHERE TableID = @TableID

 

[cheyney]

sorry, other way around

PREFIX column (char 3) and a
SEQUENCE column (char 5)

 

[nigelrivett]

if you really want to hold the data on the table and update via a trigger and hold it in a single fields then

create table mytbl (seqfld varchar(8) null, datafld varchar(20))
create table prefixtbl (prefix varchar(3), seq int)

create trigger tr_mytbl_ins on mytbl for insert
as

insert prefixtbl
select i.prefix, 0
from (select distinct prefix = left(datafld,3) from inserted) i
left join prefixtbl
on i.prefix = prefixtbl.prefix
where prefixtbl.prefix is null

declare @prefix varchar(3), @seq int
set rowcount 1
while exists (select * from mytbl where seqfld is null)
begin
select @prefix = left(datafld,3) from mytbl where seqfld is null
update prefixtbl set @seq = seq + 1, seq = seq + 1 where prefix = @prefix
update mytbl set seqfld = @prefix + right('000' + convert(varchar(4),@seq),4) where left(datafld,3) = @prefix and seqfld is null
end
set rowcount 0
go


======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[nigelrivett]

For using an identity

create table mytbl (seqfld int identity, datafld varchar(20))

then when you select

select seqfld = left(t1.datafld,3) + right('000' + convert(varchar(4),(select count(*) from mytbl t2 where left(t1.datafld,3) = left(t2.datafld,3) and t1.seqfld >= t2.seqfld)),4) ,
datafld
from mytbl t1


======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[mrees]

nigelrivett,

Ah! Now i'm with you! Sometimes I need a working example to get it through my thick skull!

That is just spot on with the Identity column. It also solve lots of other problems I am bound to have (as you probably already knew when you provided the solution) with referencing other objects.

mrees