Stored procedure syntax error
Martin got a syntax error creating a stored procedure that set a variable named ref with LET from a count(*) subquery and used it in an IF. Superboer suggested SELECT ... INTO. Uwe found the variable name 'ref' was the problem and the procedure worked with xxx instead. Martin confirmed this fixed it, though he did not understand why.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
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.