Store Procedure - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Store Procedure

Author

Topic

indr4w
Starting Member

27 Posts

Posted - 2013-09-22 : 20:50:02

Hello Mr Visakh,
how to make a simple stored procedures from the following syntax ?

CREATE PROCEDURE [dbo].[kalkulasi_gaji] @tahun char(4), @bulan varchar(15) AS
UPDATE PYTGaji SET t_jamsostek=0 where nokpj is NULL AND tahun=@tahun AND bulan=@bulan
UPDATE PYTGaji SET t_jamsostek=0 where (nokpj is not null or nokpj<>'') AND tahun=@tahun AND bulan=@bulan
-UPDATE PYTGaji SET t_jamsostek=((upah_hr*30)+t_other)*(1.19/100) where (nokpj is not NULL AND nokpj<>' ') AND tahun=@tahun AND bulan=@bulan

UPDATE PYTGaji SET t_jpk=0.06*(3080000) where status_pph<>'TK' AND (gaji+t_other>4000000) AND (nokpj is not null or nokpj<>'') AND tahun=@tahun AND bulan=@bulan
UPDATE PYTGaji SET t_jpk=0.03*(3080000) where status_pph='TK' AND (gaji+t_other>4000000) AND (nokpj is not null or nokpj<>'') AND tahun=@tahun AND bulan=@bulan
UPDATE PYTGaji SET t_jpk=0.06*((upah_hr*30)+t_other) where status_pph<>'TK' AND (gaji+t_other<4000000) AND (nokpj is not null or nokpj<>'') AND tahun=@tahun AND bulan=@bulan
UPDATE PYTGaji SET t_jpk=0.03*((upah_hr*30)+t_other) where status_pph='TK' AND (gaji+t_other<4000000) AND (nokpj is not null or nokpj<>'') AND tahun=@tahun AND bulan=@bulan
UPDATE PYTGaji SET t_jpk=0 where nokpj is NULL AND tahun=@tahun AND bulan=@bulan
UPDATE PYTGaji SET t_jpk=0 where (nokpj is not null or nokpj<>'') AND tahun=@tahun AND bulan=@bulan

UPDATE PYTGaji SET t_bagian=0 where t_bagian=NULL
UPDATE PYTGaji SET t_jabatan=0 where t_jabatan=NULL
UPDATE PYTGaji SET t_lain=0 where t_lain=NULL
UPDATE PYTGaji SET t_insentif=0 where t_insentif=NULL
UPDATE PYTGaji SET t_pendidikan=0 where t_pendidikan=NULL
UPDATE PYTGaji SET t_mskerja=0 where t_mskerja=NULL
UPDATE PYTGaji SET ulembur=0 where ulembur=NULL
UPDATE PYTGaji SET work_period=0 where work_period=NULL

UPDATE PYTGaji SET gaji=0 where gaji=NULL
UPDATE PYTGaji SET upah_hr=0 where upah_hr=NULL
UPDATE PYTGaji SET gaji=upah_hr*hrkerja where jenis_upah='H' AND tahun=@tahun AND bulan=@bulan
UPDATE PYTGaji SET upah_hr=gaji/hrkerja where jenis_upah='B' AND tahun=@tahun AND bulan=@bulan
UPDATE PYTGaji SET ulembur=((gaji)/173)*tjam_lembur where jenis_upah='H' AND tahun=@tahun AND bulan=@bulan
UPDATE PYTGaji SET ulembur=0 where jenis_upah='B' AND tahun=@tahun AND bulan=@bulan
GO

Thank's

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2013-09-23 : 01:22:06

=NULL should be replaced with IS NULL

Other than that the code looks fine

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-09-23 : 13:51:06

why do you need so many update statements? many of them conditions look similar so you can merge them into single statement
for ex:

UPDATE PYTGaji SET t_jamsostek=0 where nokpj is NULL AND tahun=@tahun AND bulan=@bulan
UPDATE PYTGaji SET t_jamsostek=0 where (nokpj is not null or nokpj<>'') AND tahun=@tahun AND bulan=@bulan

is same as

UPDATE PYTGaji SET t_jamsostek=0 where tahun=@tahun AND bulan=@bulan
as other two conditions are contradictory ones so in effect you can ignore them

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

indr4w
Starting Member

27 Posts

Posted - 2014-02-26 : 03:08:55

Hello Mr Visakh,

My question about the syntax used to simplify the many updates in stored procedures, until now I have not been able solution.
Here I include his table (PYTGaji) and stored procedures.

tahun char 4
bulan varchar 15
nik varchar 15
nama varchar 40
departemen varchar 30
divisi varchar 30
jabatan varchar 30
norek varchar 20
tmasuk decimal 9
L1 decimal 9
L2 decimal 9
L3 decimal 9
L4 decimal 9
tjam_lembur decimal 13
jenis_upah char 1
ulembur decimal 9
hrkerja decimal 9
upah_hr decimal 9
gaji decimal 9
t_jabatan decimal 9
t_bagian decimal 9
t_mskerja decimal 9
t_lain decimal 9
t_pendidikan decimal 9
t_insentif decimal 9
t_transport decimal 9
t_hadir decimal 9
t_lain1 decimal 9
t_lain2 decimal 9
Rapel decimal 9
t_thr decimal 9
t_meal decimal 9
t_comision decimal 9
t_tax decimal 9
t_tambahanlain decimal 9
p_tmasuk decimal 9
p_koperasi decimal 9
p_punishment decimal 9
p_fungsional decimal 9
p_lain decimal 9
subtotal1 decimal 9
subtotal2 decimal 9
total_diterima decimal 9
jmltunjangan decimal 9
tunjangan_lain decimal 9
premi decimal 9
jpk decimal 9
Bruto decimal 9
Premi_jabatan decimal 9
astek decimal 9
HBulan decimal 9
HTahun decimal 9
PTKP char 5
Batas decimal 9
Kena decimal 9
Akhir decimal 9
status_NPWP char 1
Psl21 decimal 9
P_Tahun decimal 9
Potongan decimal 9
PPH decimal 9
gross1 decimal 9
jbln int 4
tot_deduc decimal 9
nett decimal 9
nett_annualized decimal 9
gross2 decimal 9
non_tax_income decimal 9
taxable decimal 9
income_npwp decimal 9
income_non_npwp decimal 9
income_real decimal 9
tax_payable decimal 9
work_periodB1 char 3
work_periodB2 char 3
work_period int 4
nokpj varchar 30
keterangan varchar 50
Gol_Insentif_Kehadiran char 8
premihadir int 4
haritransport int 4
npwp varchar 30
p_jamsostek decimal 9
TglInput datetime
Tuser varchar 20
Status_pegawai varchar 20
memo varchar 50
noperkiraan varchar 30

CREATE PROCEDURE [dbo].[kalkulasi_gaji] @tahun char(4), @bulan varchar(15) AS
DECLARE @Status_PPH char(3)
DECLARE @Status_NPWP char(1)
DECLARE @NPWP varchar(30)
DECLARE @Tahun1 varchar(4)
DECLARE @Bulan1 varchar(10)
DECLARE @Batas Decimal
DECLARE @Gaji Decimal
DECLARE @NominalTransport Decimal
DECLARE @KelompokUpah Varchar(20)
DECLARE @PremiHadirJabatan Decimal

UPDATE PYTGaji SET t_bagian=0 where t_bagian is NULL
UPDATE PYTGaji SET t_jabatan=0 where t_jabatan is NULL
UPDATE PYTGaji SET t_lain=0 where t_lain is NULL
UPDATE PYTGaji SET t_insentif=0 where t_insentif is NULL
UPDATE PYTGaji SET t_pendidikan=0 where t_pendidikan is NULL
UPDATE PYTGaji SET t_mskerja=0 where t_mskerja is NULL
UPDATE PYTGaji SET ulembur=0 where ulembur is NULL
UPDATE PYTGaji SET upah_hr=0 where Upah_hr is NULL

UPDATE PYTGaji SET Gaji=upah_hr*hrkerja WHERE jenis_upah='H' AND tahun=@tahun AND bulan=@bulan
UPDATE PYTGaji SET P_Jamsostek=0.02*((30*upah_hr)+t_jabatan+t_mskerja+t_lain) where upah_hr is Not NULL AND tahun=@tahun AND bulan=@bulan
UPDATE PYTGaji SET Jmltunjangan=t_jabatan+t_bagian+t_mskerja+t_lain+t_pendidikan, Tunjangan_lain=t_pendidikan WHERE tahun=@tahun AND bulan=@bulan

UPDATE PYTGaji SET Astek=0.02*(Gaji+t_jabatan+t_mskerja+t_lain) WHERE tahun=@tahun AND bulan=@bulan
UPDATE PYTGaji SET p_tmasuk=((upah_hr*30) /21)*tmasuk where upah_hr<>0 AND tahun=@tahun AND bulan=@bulan
UPDATE PYTGaji SET p_tmasuk=0 where upah_hr=0 AND tahun=@tahun AND bulan=@bulan
UPDATE PYTGaji SET Subtotal1=gaji-p_tmasuk+t_jabatan+t_mskerja+t_lain+t_pendidikan+t_transport+t_hadir+t_lain1+t_lain2-p_jamsostek+t_bagian where upah_hr is Not NULL AND tahun=@tahun AND bulan=@bulan
UPDATE PYTGaji SET Ulembur=((gaji)/173)*tjam_lembur WHERE tahun=@tahun AND bulan=@bulan
UPDATE PYTGaji SET Subtotal2=subtotal1+ulembur+t_tambahanlain-p_lain WHERE tahun=@tahun AND bulan=@bulan
UPDATE PYTGaji SET Premi=0.0119*(gaji+jmltunjangan-tunjangan_lain) WHERE tahun=@tahun AND bulan=@bulan
UPDATE PYTGaji SET JPK=0.06*(gaji+t_jabatan+t_mskerja+t_lain) WHERE tahun=@tahun AND bulan=@bulan AND ptkp<>'TK' AND ((gaji+t_jabatan+t_mskerja+t_lain)<4725000)
UPDATE PYTGaji SET JPK=0.03*(gaji+t_jabatan+t_mskerja+t_lain) WHERE tahun=@tahun AND bulan=@bulan AND ptkp='TK' AND ((gaji+t_jabatan+t_mskerja+t_lain)<4725000)
UPDATE PYTGaji SET JPK=0.06*4725000 WHERE tahun=@tahun AND bulan=@bulan AND ptkp<>'TK' AND ((gaji+t_jabatan+t_mskerja+t_lain)>4725000)
UPDATE PYTGaji SET JPK=0.03*4725000 WHERE tahun=@tahun AND bulan=@bulan AND ptkp='TK' AND ((gaji+t_jabatan+t_mskerja+t_lain)>4725000)
UPDATE PYTGaji SET Bruto=(Gaji+Jmltunjangan+Premi+JPK+ULembur) WHERE tahun=@tahun AND bulan=@bulan
UPDATE PYTGaji SET Premi_Jabatan=Bruto*0.05 WHERE tahun=@tahun AND bulan=@bulan
UPDATE PYTGaji SET Hbulan=Bruto-Premi_jabatan-Astek-P_tmasuk WHERE tahun=@tahun AND bulan=@bulan
UPDATE PYTGaji SET HTahun=Hbulan*12 WHERE tahun=@tahun AND bulan=@bulan

UPDATE PYTGaji SET Kena=HTahun-Batas WHERE tahun=@tahun AND bulan=@bulan
UPDATE PYTGaji SET Akhir=0 WHERE tahun=@tahun AND bulan=@bulan AND Kena<=0
UPDATE PYTGaji SET Akhir=Kena WHERE tahun=@tahun AND bulan=@bulan AND Kena>0
UPDATE PYTGaji SET Psl21=0.06 WHERE tahun=@tahun AND bulan=@bulan AND Status_NPWP='N'
UPDATE PYTGaji SET Psl21=0.05 WHERE tahun=@tahun AND bulan=@bulan AND Status_NPWP='Y'
UPDATE PYTGaji SET P_Tahun=Psl21*Akhir WHERE tahun=@tahun AND bulan=@bulan
UPDATE PYTGaji SET Potongan=P_Tahun/12 WHERE tahun=@tahun AND bulan=@bulan
UPDATE PYTGaji SET PPH=Potongan WHERE tahun=@tahun AND bulan=@bulan
UPDATE PYTGaji SET Total_diterima=Subtotal2-PPH-P_Koperasi+Rapel WHERE tahun=@tahun AND bulan=@bulan
GO

Thank you for your attention and answer

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources