October 13, 2005 at 8:09 am
Hi,
I want to pick up the value in a table and add one to it as the next sequence number. This sequence number will start at one at the start of every month. I want to ensure that I get the next sequence number each time I do an insert. In the code below, does the UpdateLock used by the INSERT statement ensure that nobody else can write to the table while it is picking up the Sequence No.?
INSERT Table1 (Field1, Description, SeqNo, InsMonth) SELECT @Field1, @Description, NextSeqNo = CASE
WHEN (SELECT Max(SeqNo) FROM Table1 WHERE InsMonth = Month(GetDate()) IS NULL THEN 1
ELSE (SELECT Max(SeqNo) +1 FROM Table1 WHERE InsMonth = Month(GetDate())
END,
@InsMonth
October 13, 2005 at 8:28 am
Is this code and a table structure you cannot change? Why not use an IDENTITY field rather than searching for a new when someone else could be in that table?
I wasn't born stupid - I had to study.
October 13, 2005 at 8:42 am
Hi,
The reason it has to be this way is that the SeqNo is used in a number that runs per month i.e. 05-OCT-213 where 213 is the sequence number. The client wants this number reset every month i.e. the first one in Nov will be 05-NOV-001, therefore it can't be an IDENTITY
October 13, 2005 at 8:48 am
I have seen postings like this before on this site. Maybe you could do a search and see if you can find how people resolved it? There should be multiple posts...
I wasn't born stupid - I had to study.
October 13, 2005 at 12:53 pm
The way to implement this is to use a separated table which contains the next ID as column and you can readit and updated in one statement with no locks or table scans needed
* Noel
October 14, 2005 at 9:38 am
how bout a nice little function to turn your code into:
INSERT Table1 (Field1, Description, SeqNo, InsMonth) SELECT @Field1, @Description, NextSeqNo = NextSeqNum,
@InsMonth
---------------------------------
create table Counters(
YearMonth int -- YYYYMM
,CounterValue int
)
insert into Counters (200510,1)
insert into Counters (200511,1)
insert into Counters (200512,1)
insert into Counters (200601,1)
insert into Counters (200602,1)
insert into Counters (200603,1)
insert into Counters (200604,1)
insert into Counters (200605,1)
insert into Counters (200606,1)
insert into Counters (200607,1)
insert into Counters (200608,1)
insert into Counters (200609,1)
insert into Counters (200610,1)
insert into Counters (200611,1)
insert into Counters (200612,1)
create function dbo.NextSeqNum
returns int
as
begin
declare @NewSeqNum int
declare @CurrentYearMonth int
set @CurrentYearMonth = year(getdate()) * 100 + month(getdate())
begin transaction
select @NewSeqNum = CounterValue + 1
from Counters
where YearMonth = @CurrentYearMonth
update Counters
set CounterValue = @NewSeqNum
where YearMonth = @CurrentYearMonth
commit transaction
return @NewSeqNum
end
October 14, 2005 at 9:39 am
OOPS! sorry, should have initialized CounterValue to zero!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply