Stored procedure syntax error

Hello everybody. I know this is kind of lame problem, but I realy cannot get over it. So, I want create a procedure: CREATE PROCEDURE delete_subject_if_no_program(subjectoid INT)DEFINE ref INT; LET ref=(SELECT count(*) FROM dbs_sub_spr_relation WHERE ssr_sub_oid=subjectoid); IF ref=0 THEN ---SOME DELETE STATEMENTS END IF; END PROCEDURE; The problem is I always get a syntax error. The problem is for sure in the IF statement itself. even i try CREATE PROCEDURE delete_subject_if_no_program(subjectoid INT)DEFINE ref INT; LET ref=(SELECT count(*) FROM dbs_sub_spr_relation WHERE ssr_sub_oid=subjectoid); IF ref=0 THEN LET ref=1; END IF; END PROCEDURE; im getting a syntax error. I searched the web but nothing. Please, any ideas are welcomed. I will appreciate any help thx. Martin

Try: CREATE PROCEDURE delete_subject_if_no_program(subjectoid INT) DEFINE ref INT; SELECT count(*) into ref FROM dbs_sub_spr_relation WHERE ssr_sub_oid=subjectoid; IF ref=0 THEN LET ref=1; END IF; END PROCEDURE; Superboer. On 22 mei, 10:13, MartinJe...@gmail.com wrote: > Hello everybody. > I know this is kind of lame problem, but I realy cannot get over it. > So, I want create a procedure: > > CREATE PROCEDURE delete_subject_if_no_program(subjectoid INT)> DEFINE ref INT; > LET ref=(SELECT count(*) FROM dbs_sub_spr_relation WHERE > ssr_sub_oid=subjectoid); > IF ref=0 THEN > ---SOME DELETE STATEMENTS > END IF; > END PROCEDURE; > > The problem is I always get a syntax error. The problem is for sure in > the IF statement itself. > even i try > > CREATE PROCEDURE delete_subject_if_no_program(subjectoid INT)> DEFINE ref INT; > LET ref=(SELECT count(*) FROM dbs_sub_spr_relation WHERE > ssr_sub_oid=subjectoid); > IF ref=0 THEN LET ref=1; > END IF; > END PROCEDURE; > > im getting a syntax error. I searched the web but nothing. > Please, any ideas are welcomed. I will appreciate any help > thx. > Martin

On 22 Mai, 10:13, MartinJe...@gmail.com wrote: > Hello everybody. > I know this is kind of lame problem, but I realy cannot get over it. > So, I want create a procedure: > > CREATE PROCEDURE delete_subject_if_no_program(subjectoid INT)> DEFINE ref INT; > LET ref=(SELECT count(*) FROM dbs_sub_spr_relation WHERE > ssr_sub_oid=subjectoid); > IF ref=0 THEN > ---SOME DELETE STATEMENTS > END IF; > END PROCEDURE; > > The problem is I always get a syntax error. The problem is for sure in > the IF statement itself. > even i try > > CREATE PROCEDURE delete_subject_if_no_program(subjectoid INT)> DEFINE ref INT; > LET ref=(SELECT count(*) FROM dbs_sub_spr_relation WHERE > ssr_sub_oid=subjectoid); > IF ref=0 THEN LET ref=1; > END IF; > END PROCEDURE; > > im getting a syntax error. I searched the web but nothing. > Please, any ideas are welcomed. I will appreciate any help > thx. > Martin Hi, Martin, the problem seems to be the name of the variable 'ref'. This one works: CREATE PROCEDURE delete_subject_if_no_program(subjectoid INT)DEFINE xxx INT; LET xxx=(SELECT count(*) FROM dbs_sub_spr_relation WHERE ssr_sub_oid=subjectoid); IF xxx=0 THEN ---SOME DELETE STATEMENTS END IF; END PROCEDURE; good luck, Uwe.

O M G. Thank you very much. If i've got to be honest i dont understand why it didn't work with variable ref. Why it worked without the IF statement. Anyway, Uwe, thanks again ubookwood@rosacker-online.de nap'sal(a): > On 22 Mai, 10:13, MartinJe...@gmail.com wrote: > > Hello everybody. > > I know this is kind of lame problem, but I realy cannot get over it. > > So, I want create a procedure: > > > > CREATE PROCEDURE delete_subject_if_no_program(subjectoid INT)> > DEFINE ref INT; > > LET ref=(SELECT count(*) FROM dbs_sub_spr_relation WHERE > > ssr_sub_oid=subjectoid); > > IF ref=0 THEN > > ---SOME DELETE STATEMENTS > > END IF; > > END PROCEDURE; > > > > The problem is I always get a syntax error. The problem is for sure in > > the IF statement itself. > > even i try > > > > CREATE PROCEDURE delete_subject_if_no_program(subjectoid INT)> > DEFINE ref INT; > > LET ref=(SELECT count(*) FROM dbs_sub_spr_relation WHERE > > ssr_sub_oid=subjectoid); > > IF ref=0 THEN LET ref=1; > > END IF; > > END PROCEDURE; > > > > im getting a syntax error. I searched the web but nothing. > > Please, any ideas are welcomed. I will appreciate any help > > thx. > > Martin > > Hi, Martin, > the problem seems to be the name of the variable 'ref'. > This one works: > CREATE PROCEDURE delete_subject_if_no_program(subjectoid INT)> DEFINE xxx INT; > LET xxx=(SELECT count(*) FROM dbs_sub_spr_relation WHERE > ssr_sub_oid=subjectoid); > IF xxx=0 THEN > ---SOME DELETE STATEMENTS > END IF; > END PROCEDURE; > > good luck, > Uwe.

O M G. Thank you very much. If i've got to be honest i dont understand why it didn't work with variable ref. Why it worked without the IF statement. Anyway, Uwe, thanks again ubookwood@rosacker-online.de nap'sal(a): > On 22 Mai, 10:13, MartinJe...@gmail.com wrote: > > Hello everybody. > > I know this is kind of lame problem, but I realy cannot get over it. > > So, I want create a procedure: > > > > CREATE PROCEDURE delete_subject_if_no_program(subjectoid INT)> > DEFINE ref INT; > > LET ref=(SELECT count(*) FROM dbs_sub_spr_relation WHERE > > ssr_sub_oid=subjectoid); > > IF ref=0 THEN > > ---SOME DELETE STATEMENTS > > END IF; > > END PROCEDURE; > > > > The problem is I always get a syntax error. The problem is for sure in > > the IF statement itself. > > even i try > > > > CREATE PROCEDURE delete_subject_if_no_program(subjectoid INT)> > DEFINE ref INT; > > LET ref=(SELECT count(*) FROM dbs_sub_spr_relation WHERE > > ssr_sub_oid=subjectoid); > > IF ref=0 THEN LET ref=1; > > END IF; > > END PROCEDURE; > > > > im getting a syntax error. I searched the web but nothing. > > Please, any ideas are welcomed. I will appreciate any help > > thx. > > Martin > > Hi, Martin, > the problem seems to be the name of the variable 'ref'. > This one works: > CREATE PROCEDURE delete_subject_if_no_program(subjectoid INT)> DEFINE xxx INT; > LET xxx=(SELECT count(*) FROM dbs_sub_spr_relation WHERE > ssr_sub_oid=subjectoid); > IF xxx=0 THEN > ---SOME DELETE STATEMENTS > END IF; > END PROCEDURE; > > good luck, > Uwe.