Sybase Stored procedure error

hello..

I'm using sybase database. I tried to create stored procedure in DBEAVER Tool but it gives error below :

Below are my stored procedure

create procedure lecasuser.myclass_ins_classTest1
(
@stafno VARCHAR(10),
@term VARCHAR(10),
@kodk VARCHAR(15),
@kump VARCHAR(5),
@lat VARCHAR(20),
@lng VARCHAR(20)
)
as
begin
declare @cid_class int, @lokasi varchar(50),@msg varchar(50),@status varchar(1),@bil int,@err int
select term,stafno,kodk,namak, kump,masa, hari,bilik,semester,table_name,thoff_view
into #kursus
from lecas_ug_lecturer_pidx01
where term = @term
and kodk = @kodk
and kump = @kump
and stafno = @stafno
and thoff_view >= getdate()
union
select term,stafno_luar,kodk,namak, kump,masa, hari,bilik,semester,table_name,thoff_view_tugas
from lecas_ug_lecturer_pidx01
where term = @term
and kodk = @kodk
and kump = @kump
and stafno_luar = @stafno
and thoff_view_tugas >= getdate()
insert into #kursus
select term,stafno,kodk,namak, kump,masa, hari,bilik,semester,table_name,thoff_view
from lecas_ug_lecturer_pidx02
where term = @term
and kodk = @kodk
and kump = @kump
and stafno = @stafno
and convert(varchar,thoff_view,103) = convert(varchar,getdate(),103)
if exists (select kodk from #kursus)
if not exists (
select term from my_class
where term = @term
and kodk = @kodk
and kump = @kump)
insert into my_class (term, kodk, kump, stafno, bil, stud,jnsclass,cthcreate)
select term , kodk , kump , stafno , bil = 1, stud = 'U',
jnsclass = 'C', cthcreate = getdate()
from #kursus
select @lokasi = bilik from #kursus
select @cid_class = cid_class
from my_class
where term = @term
and kodk = @kodk
and kump = @kump
insert into my_class_dtl (id_class,thmula,thtamat,rujukan,lokasi,mlat,mlng,thcreate,stafno_lecturer,jenisKelas,catatan)
select id_class = @cid_class,
thmula = getdate(),
thtamat = getdate(),
rujukan = convert(varchar, getdate(), 105)+' '+convert(varchar, getdate(), 108),
lokasi = @lokasi,
mlat = @lat,
mlng = @lng,
thcreate = getdate(),
stafno_lecturer = @stafno,
jenisKelas = '',
catatan = ''
select @err = @@error
if @err <> 0
rollback tran
select @msg="Error Insert record",@status="-1"
return @err
else
select @bil=count(id_class)
from my_class_dtl
where id_class=@cid_class
update my_class set bil=@bil
where cid_class=@cid_class
select @err = @@error
if @err <> 0
rollback tran
select @msg="Error update my_class",@status="-1"
return @err
else
select msg="Class succesfully registered",status="1"
end

Can someone help me to fix the error.

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Sachin Singh5y ago
always use begin after IF or Else and End it properly , after adding begin and end ,check whether each block gets proper closing(End) or not.
+1
Rajanikant Hawaldar5y ago
Hello Hazel, The I think the problem is in if @err <> 0 block - you need to put this code in BEGIN/END as well.
if @err <> 0
begin
rollback tran
select @msg="Error Insert record",@status="-1"
return @err
end
else
begin
select @bil=count(id_class)
from my_class_dtl
where id_class=@cid_class
update my_class set bil=@bil
where cid_class=@cid_class
select @err = @@error
end
if @err <> 0
begin
rollback tran
select @msg="Error update my_class",@status="-1"
return @err
end
else
begin
select msg="Class succesfully registered",status="1"
end
+2