By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,779 Members | 1,078 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,779 IT Pros & Developers. It's quick & easy.

Statement sequence in SQL Stored Procedure

P: n/a
Is there some kind of requirement that a DECLARE CONTINUE HANDLER statement
follow the DECLARE CURSOR statement in an SQL stored procedure?

I am running DB2 V8.2.1 on Windows XP and am writing my first few SQL stored
procedures. Given this fragment (there is more code following the last
statement):

----------------------------------------------------------------------------
-----------------------------------

DROP PROCEDURE foo (smallint, dec(11,2) ) @

CREATE PROCEDURE foo
(IN in_edlevel smallint, --IN: the education level
OUT out_avg_salary dec(11,2)) --OUT: the average salary
dynamic result sets 0
LANGUAGE SQL
BEGIN
declare local_avg_salary dec(11,2);

declare local_eof_flag smallint default 0;
declare local_eof condition for SQLSTATE '02000';

declare cur1 cursor for
select coalesce(avg(salary),0)
from emp
where edlevel = in_edlevel;

declare continue handler for local_eof set local_eof_flag = 1;

----------------------------------------------------------------------------
-----------------------------------

the SQL procedure is created just fine. However, if I move the DECLARE
CONTINUE HANDLER statement so that it precedes the DECLARE CURSOR statement,
the script will not work and fails on SQL0104N on the "DECLARE local_eof
condition" statement within the CREATE PROCEDURE statement.

I'm a bit puzzled by this since the cursor declaration doesn't directly
reference local_eof_flag, the local_eof condition, or the continue handler.
It seems to me that the procedure should be created just fine in both cases.

Can anyone explain why the continue handler MUST follow the cursor
declaration? Is this - and any related "gotchas" - documented in the manuals
somewhere? I'm not sure where to look for something like this.

--
Rhino
---
rhino1 AT sympatico DOT ca
"There are two ways of constructing a software design. One way is to make it
so simple that there are obviously no deficiencies. And the other way is to
make it so complicated that there are no obvious deficiencies." - C.A.R.
Hoare
Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a

"Rhino" <rh****@NOSPAM.sympatico.ca> wrote in message
news:I%*******************@news20.bellglobal.com.. .
Is there some kind of requirement that a DECLARE CONTINUE HANDLER statement follow the DECLARE CURSOR statement in an SQL stored procedure?


< snip example >

Yes. But it's documented in a horribly round-about way.

If you look at the documentation for "DECLARE PROCEDURE (SQL)", it points to
the definition of "SQL-procedure-body".

Surfing on over to the "Compound SQL (Procedure) Statement", which defines
SQL-procedure-body, the ordering becomes apparent.

http://publib.boulder.ibm.com/infoce...n/r0004239.htm

In particular, notice that "DECLARE-CURSOR statement" is immediately
followed by "handler declaration".

Thus, your exception handlers must immediately follow your cursor
definitions.

--
Matt Emmerton
Nov 12 '05 #2

P: n/a
Thanks, Matt; that was very helpful!

Rhino

"Matt Emmerton" <me******@nospam.yahoo.com> wrote in message
news:OJ********************@rogers.com...

"Rhino" <rh****@NOSPAM.sympatico.ca> wrote in message
news:I%*******************@news20.bellglobal.com.. .
Is there some kind of requirement that a DECLARE CONTINUE HANDLER statement
follow the DECLARE CURSOR statement in an SQL stored procedure?


< snip example >

Yes. But it's documented in a horribly round-about way.

If you look at the documentation for "DECLARE PROCEDURE (SQL)", it points

to the definition of "SQL-procedure-body".

Surfing on over to the "Compound SQL (Procedure) Statement", which defines
SQL-procedure-body, the ordering becomes apparent.

http://publib.boulder.ibm.com/infoce...n/r0004239.htm
In particular, notice that "DECLARE-CURSOR statement" is immediately
followed by "handler declaration".

Thus, your exception handlers must immediately follow your cursor
definitions.

--
Matt Emmerton

Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.