Contoh-contoh SP dan AutoNumber

ALTER PROCEDURE SP_InsertDaftar

@nama varchar(50),
@lp varchar(50),
@tgldaftar datetime,
@jurusan varchar(50)

AS
BEGIN

declare @nodaftar varchar(50)
declare @noakhir int
declare @nobaru int

select @noakhir = Max(Convert(int,substring(NoDaftar,8,4))) from TDaftar

if(@noakhir is null)
begin
set @nobaru = 1
end
else
begin
set @nobaru = @noakhir + 1
end

set @nodaftar = Convert(varchar,datepart(yyyy,@tgldaftar)) + '/' +
@lp + '-' + right('0000' + Convert(varchar,@nobaru),4) + '/' +
@jurusan

insert into TDaftar
values(@nodaftar,@nama,@lp,@tgldaftar,@jurusan)

END
GO

exec SP_InsertDaftar 'Test3','P','1/1/2011','DK'
select * from TDaftar

-------------------------------------------------------------------------------------

CREATE PROCEDURE SP_InsertPegawai

@thnmasuk datetime,
@nama varchar(50)

AS
BEGIN

declare @nopegawai varchar(50)
declare @noakhir int
declare @nobaru int

select @noakhir = Max(Convert(int,substring(NoPegawai,1,5))) from TPegawai

if(@noakhir is null)
begin
set @nobaru = 1
end
else
begin
set @nobaru = @noakhir + 1
end

set @nopegawai = right('00000' + Convert(varchar,@nobaru),5) +
'-' + Convert(varchar,datepart(yyyy,@thnmasuk)) + '-' +
substring(@nama,1,1)

insert into TPegawai
values(@nopegawai,@thnmasuk,@nama)

END
GO

exec SP_InsertPegawai '5/6/2005','Bebas'
select * from TPegawai

-------------------------------------------------------------------------------------

CREATE PROCEDURE SP_TambahTBPO
@TglPO datetime, @KodeSupplier varchar(10)
AS
BEGIN
DECLARE @NOAKHIR INT
DECLARE @NOBaru INT
DECLARE @NomorPO varchar(13)

--1.Cari Nomor Akhir
select @NoAkhir = convert(int, Max(right(NomorPO,5)))
from tbPO
where substring(NomorPO,4,4) = convert(char(4), Datepart(yyyy, @TglPO))

--2.Cari Nomor Baru
if @NoAkhir is null
Set @Nobaru=1
else
Set @NoBaru = @NoAkhir + 1

Set @NomorPO = 'PO-' + convert(char(4), Datepart(yyyy, @TglPO )) + '-' +
RIGHT('00000'+Convert(varchar, @NoBaru), 5)

--3.Insert Data baru
Insert tbPO (NomorPO, TglPO, KodeSupplier) Values (@NomorPO, @TglPO, @KodeSupplier)
END

exec SP_TambahTBPO'6/1/2005', 'S-00005'



-------------------------------------------------------------------------------------

ALTER PROCEDURE SP_TambahGudang
@TglPO datetime, @Gudang varchar(10), @KodeSupplier varchar(10)
AS
BEGIN
DECLARE @NOAKHIR INT
DECLARE @NOBaru INT
DECLARE @NomorPO varchar(50)

--1.Cari Nomor Akhir
select @NoAkhir = Max(convert(int,(right(NomorPO,5))))
from TGudang
where (substring(NomorPO,4,4) = convert(varchar(4), Datepart(yyyy, @TglPO)))and (substring(NomorPO,9,2) = Convert(varchar(2),@Gudang))

--2.Cari Nomor Baru
if @NoAkhir is null
Set @Nobaru=1
else
Set @NoBaru = @NoAkhir + 1

Set @NomorPO = 'PO-' + convert(varchar(4), Datepart(yyyy, @TglPO )) + '-' + @Gudang + '-' +
RIGHT('00000'+Convert(varchar(5), @NoBaru), 5)


--3.Insert Data baru
Insert TGudang (NomorPO, TglPO, Gudang, KodeSupplier) Values (@NomorPO, @TglPO, @Gudang, @KodeSupplier)
END

exec SP_TambahGudang '10/16/2010','G3','SS'

-------------------------------------------------------------------------------------

ALTER PROCEDURE SP_TambahBarang
@NamaBarang varchar(50), @Stock int
AS
BEGIN
DECLARE @NOAKHIR INT
DECLARE @NOBaru INT
DECLARE @Kode varchar(50)

--1.Cari Nomor Akhir
select @NoAkhir = Max(convert(int,(right(KodeBarang,3))))
from TBarang
where left(KodeBarang,1) = left(@NamaBarang,1)

--2.Cari Nomor Baru
if @NoAkhir is null
Set @Nobaru=1
else
Set @NoBaru = @NoAkhir + 1

Set @Kode = left(@NamaBarang,1) + RIGHT('000'+Convert(varchar(5), @NoBaru), 3)


--3.Insert Data baru
Insert TBarang
Values (@Kode, @NamaBarang, @Stock)
END

exec SP_TambahBarang 'Bath Tub Toto 34523',5
select * from TBarang