At 11:05 AM 1/09/2003 +0000, you wrote:

>hi,
>I am writting stored procedure which will fire many delete queries
>on the basis of the number as a input parameter.When in the stored
>procedure i use only one delete statement and use :calapp_id in
>it .it gets created and committed.but if i use more than one delete
>statement which use same variable in where clause,then create
>procedure query gets executed bt commit gives an error.I have given
>the code ->
>
>eg:-
>
>SET TERM ##;
>CREATE PROCEDURE Z(
>CALAPP_ID INTEGER
>)
>AS
>BEGIN
>delete from calappparams A where A.caid = :calapp_id;
>delete from hostedapps where caid = :calapp_id;
>END ##;
>SET TERM ##
>
>//this gets compled but when i say commit;
>error is -
>Statement #1:
>invalid request BLR at offset 90
>bad parameter number

Then it doesn't get compiled. Compilation happens *after* you commit the
procedure declaration. This didn't commit, it failed.

1. Get rid of the aliases in Statement #1
2. Correct the syntax of the second SET TERM statement
SET TERM ;##
and try again.
3. If there are still problems after that, tell us what server and version
you are using.

Please don't blitz the list with multiple copies of the same
message. Yahoo is slow today so you just need to wait for your msgs to appear.

^heLen