Auto Number di MSSQL

2008 August 28 at 4:02 pm Leave a comment

–hpus table temporary #tmp kali aja udah ada🙂

Drop Table #tmp

–buat table temporary #tmp
Create table #tmp (
data_id int identity(1,1),
po_no Char(12)
)

Declare @code varchar(10), @run_no Varchar(4),@len int
SET @code=’PO’
SET @run_no=’0000′–untuk menetukan berapa digit Auto Run-nya
SET @len= len(@run_no)
—-
–Insert data kesatu
—–
insert into #tmp(po_no)
select @code+’/’+Convert(char(4),getdate(),112)+’/’+
Right(@run_no+Coalesce(
cAST (cAST(MAX(right(po_no,@len)) AS INT)+1 aS Varchar(5)),’1′)
,@len)
from #tmp
Where substring(po_no,@len+2,4)=Convert(char(4),getdate(),112)
—-
–Insert data kedua
—-
insert into #tmp(po_no)
select @code+’/’+Convert(char(4),getdate(),112)+’/’+
Right(@run_no+Coalesce(
cAST (cAST(MAX(right(po_no,@len)) AS INT)+1 aS Varchar(10)),’1′)
,@len)
from #tmp
—-
–Insert data seterusnya
—-
–kemudian lihat data di table temporary #tmp

select * from #tmp

Entry filed under: MSSQL. Tags: .

Kumpulan Hadits Sedekah Linked Server di MSSQL 2000

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Trackback this post  |  Subscribe to the comments via RSS Feed


Categories


%d bloggers like this: