Sunday, December 13, 2009

Generate System Column ID based on Format Required in SQL

If we want to make a generate id column in table which format is 'TR-091214-001' where,
- tr is define transaction
- 091213 is define yymmdd
- 001 is define increment number of transaction each day

we can make a simple logic and validation for that.

here the steps :

-- step 1 create table
create table testgenid
(
tr_id varchar(30) not null,
primary key(tr_id)
)

-- step 2 generate id
declare @gen_id varchar(30)
set @gen_id = (
select 'TR-'+ right(convert(varchar,getdate(),112),6) + '-' + right('00' + cast((cast(right(isnull(max(tr_id),'000'),3) as int) + 1) as varchar),3)
from testgenid
where tr_id like '%' + right(convert(varchar,getdate(),112),6) + '%')

print @gen_id

insert into testgenid values(@gen_id)

-- step 3 see the result
select * From testgenid

the result is : TR-091214-001

happy coding ^^

No comments:

Post a Comment