August 30, 2011 at 7:36 am
I'm using SQL Server 2005. And I'm trying to develop a stored proc that will automate the creation of a custom equipment id . Format ABC-YEAR-0001 ex: ABC-2011-0001, ABC-2011-0002, ABC-2011-0003 . The client wants to reset the last 4 digits at the start of each new year. Ex: ABC-2012-0001. The ABC portion never changes. Only the the YYYY and 0001 parts change. The equipment id will not be the primary key. It's meant to be more of barcode or equipment identifier.
I need help with the logic.
How can I accomplish this in a stored proc or function?
Thanks
August 30, 2011 at 7:49 am
You could have a function query the most recent value, compare the year part of it to DatePart(Year, Getdate()), and if it's the same year, add 1 to the number. It'll require conversion to a string to pad it with leading 0's, of course.
As with any manual row-value incrementer, you'll need to make sure you manage table locking explicitly, to avoid accidentally assigning the same value to more than one concurrent insert. Isolation levels can help with that, or table hints.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 30, 2011 at 7:58 am
Do you have a sample that I can review?
Thanks, appreciated.
August 31, 2011 at 8:54 am
Something like this:
create table dbo.IDTest (
ID int identity,
EquipmentID char(13) primary key);
go
insert into dbo.IDTest(EquipmentID)
values('ABC-2010-0001'); -- Seed value, to test year-vs-year code
go
create proc dbo.IDGenerator
(@ID_out char(13) output)
as
set nocount on;
set transaction isolation level repeatable read;
declare @PriorID char(13), @Numeric smallint, @Year int;
select @PriorID = (select top 1 EquipmentID
from dbo.IDTest
order by ID desc);
select @Year = SUBSTRING(@PriorID, 5, 4), @Numeric = SUBSTRING(@PriorID, 10, 4);
if @Year < DATEPART(year, getdate())
set @Numeric = 1;
else
set @Numeric = @Numeric + 1;
set @ID_out =
SUBSTRING(@PriorID, 1, 3)
+ '-'
+ CAST(datepart(year, getdate()) as CHAR(4))
+ '-'
+ right('0000' + CAST(@Numeric as varchar(4)), 4);
insert into dbo.IDTest (EquipmentID)
values (@ID_out);
go
declare @EquipID char(13);
exec dbo.IDGenerator @EquipID output;
select @EquipID;
go
drop table dbo.IDTest;
drop proc dbo.IDGenerator;
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 1, 2011 at 4:17 pm
Thanks alot. Much appreciated.
September 2, 2011 at 6:23 am
You're welcome.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply