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

set variables to be used in creation of result set.

P: n/a
I was wondering if there is a way to create a stored procedure that I
can
DECLARE Variables
then set these variables using a select statment -- up to here works.
then use a different statement to generate a result set.

I've been trying to do this with using a Cursor but I get an error that
I can't DECLARE a cursor after a SQL statement has been run.

I don't need it to be returned in a Cursor if there is another way. I
just need a result set.

Apr 18 '06 #1
Share this Question
Share on Google+
10 Replies


P: n/a
el*********@gmail.com wrote:
I was wondering if there is a way to create a stored procedure that I
can
DECLARE Variables
then set these variables using a select statment -- up to here works.
then use a different statement to generate a result set.

I've been trying to do this with using a Cursor but I get an error that
I can't DECLARE a cursor after a SQL statement has been run.
Wrap the DECLARE CURSOR statement in its own BEGIN ... END block.
I don't need it to be returned in a Cursor if there is another way. I
just need a result set.


Result sets and cursors are tied to each other.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Apr 18 '06 #2

P: n/a
thank you.

Apr 18 '06 #3

P: n/a
Knut Stolze <st****@de.ibm.com> wrote:
el*********@gmail.com wrote:
I was wondering if there is a way to create a stored procedure that I
can DECLARE Variables then set these variables using a select statment
-- up to here works.
then use a different statement to generate a result set.

I've been trying to do this with using a Cursor but I get an error that
I can't DECLARE a cursor after a SQL statement has been run.


Wrap the DECLARE CURSOR statement in its own BEGIN ... END block.


Was this added in v8?

I got "...LINE 20 COL 1 NESTED COMPOUND STATEMENTS NOT ALLOWED"
as a build error when I tried that, but I'm using v7.

--
J. Moreno
Apr 25 '06 #4

P: n/a
J. Moreno wrote:
Knut Stolze <st****@de.ibm.com> wrote:
el*********@gmail.com wrote:
I was wondering if there is a way to create a stored procedure that I
can DECLARE Variables then set these variables using a select statment
-- up to here works.
then use a different statement to generate a result set.

I've been trying to do this with using a Cursor but I get an error that
I can't DECLARE a cursor after a SQL statement has been run.

Wrap the DECLARE CURSOR statement in its own BEGIN ... END block.


Was this added in v8?

I got "...LINE 20 COL 1 NESTED COMPOUND STATEMENTS NOT ALLOWED"
as a build error when I tried that, but I'm using v7.

DB2 for zOS presumably V7?
Try a dynamic cursor declaration, that should work.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Apr 26 '06 #5

P: n/a
Serge Rielau <sr*****@ca.ibm.com> wrote:
J. Moreno wrote:
Knut Stolze <st****@de.ibm.com> wrote: -snip-
Wrap the DECLARE CURSOR statement in its own BEGIN ... END block.
Was this added in v8?

I got "...LINE 20 COL 1 NESTED COMPOUND STATEMENTS NOT ALLOWED"
as a build error when I tried that, but I'm using v7.

DB2 for zOS presumably V7?


Yep.
Try a dynamic cursor declaration, that should work.


A stored procedure with this....
-- start here
BEGIN
DECLARE cursor1 INSENSITIVE SCROLL CURSOR WITH RETURN FOR
SELECT count(aColumn) FROM aTable;

-- Cursor left open for client application
OPEN cursor1;
END:
-- end here

compiles. Wrapping that in an extra begin/end gets the above error
message.

--
J. Moreno
Apr 26 '06 #6

P: n/a
J. Moreno wrote:
Serge Rielau <sr*****@ca.ibm.com> wrote:
J. Moreno wrote:
Knut Stolze <st****@de.ibm.com> wrote: -snip- Wrap the DECLARE CURSOR statement in its own BEGIN ... END block.
Was this added in v8?

I got "...LINE 20 COL 1 NESTED COMPOUND STATEMENTS NOT ALLOWED"
as a build error when I tried that, but I'm using v7.

DB2 for zOS presumably V7?


Yep.
Try a dynamic cursor declaration, that should work.


A stored procedure with this....
-- start here
BEGIN
DECLARE cursor1 INSENSITIVE SCROLL CURSOR WITH RETURN FOR
SELECT count(aColumn) FROM aTable;

-- Cursor left open for client application
OPEN cursor1;
END:
-- end here

compiles. Wrapping that in an extra begin/end gets the above error
message.

Well, yes, that's what you said. Here is what I meant:

CREATE PROCEDURE p()
BEGIN
DECLARE var CHAR(1);
DECLARE txt VARCHAR(100);
DECLARE stmt STATEMENT;
DECLARE cur CURSOR WITH RETURN FOR stmt;
SET txt = 'SELECT * FROM SYSIBM.SYSDUMMY1 WHERE IBMREQD = ?';
PREPARE stmt FROM txt;
SET var = 'Y';
OPEN cur USING var;
END
@

CALL P()
@
Result set 1
--------------

IBMREQD
-------
Y

1 record(s) selected.

Return Status = 0

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Apr 27 '06 #7

P: n/a
Serge Rielau <sr*****@ca.ibm.com> wrote:
J. Moreno wrote: -snip nested compound statement not building inside stored procedure-
compiles. Wrapping that in an extra begin/end gets the above error
message.

Well, yes, that's what you said. Here is what I meant:


Thanks for the example, I think I understand now.
CREATE PROCEDURE p()
BEGIN
DECLARE var CHAR(1);
DECLARE txt VARCHAR(100);
DECLARE stmt STATEMENT;
DECLARE cur CURSOR WITH RETURN FOR stmt;
SET txt = 'SELECT * FROM SYSIBM.SYSDUMMY1 WHERE IBMREQD = ?';
PREPARE stmt FROM txt;
SET var = 'Y';
OPEN cur USING var;
END


Doesn't build. LINE 14 COL 17 UNSUPPORTED DATA TYPE DISTINCT TYPE
ENCOUNTERED IN SQL VARIABLE STMT

Off to do some more googling...

--
J. Moreno
Apr 27 '06 #8

P: n/a
J. Moreno wrote:
Serge Rielau <sr*****@ca.ibm.com> wrote:
J. Moreno wrote:

-snip nested compound statement not building inside stored procedure-
compiles. Wrapping that in an extra begin/end gets the above error
message.

Well, yes, that's what you said. Here is what I meant:


Thanks for the example, I think I understand now.
CREATE PROCEDURE p()
BEGIN
DECLARE var CHAR(1);
DECLARE txt VARCHAR(100);
DECLARE stmt STATEMENT;
DECLARE cur CURSOR WITH RETURN FOR stmt;
SET txt = 'SELECT * FROM SYSIBM.SYSDUMMY1 WHERE IBMREQD = ?';
PREPARE stmt FROM txt;
SET var = 'Y';
OPEN cur USING var;
END


Doesn't build. LINE 14 COL 17 UNSUPPORTED DATA TYPE DISTINCT TYPE
ENCOUNTERED IN SQL VARIABLE STMT

Off to do some more googling...

Try removing
DECLARE stmt STATEMENT;

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Apr 27 '06 #9

P: n/a
Serge Rielau <sr*****@ca.ibm.com> wrote:
J. Moreno wrote:
Serge Rielau <sr*****@ca.ibm.com> wrote: -snip-
CREATE PROCEDURE p()
BEGIN
DECLARE var CHAR(1);
DECLARE txt VARCHAR(100);
DECLARE stmt STATEMENT;
DECLARE cur CURSOR WITH RETURN FOR stmt;
SET txt = 'SELECT * FROM SYSIBM.SYSDUMMY1 WHERE IBMREQD = ?';
PREPARE stmt FROM txt;
SET var = 'Y';
OPEN cur USING var;
END


Doesn't build. LINE 14 COL 17 UNSUPPORTED DATA TYPE DISTINCT TYPE
ENCOUNTERED IN SQL VARIABLE STMT

Off to do some more googling...

Try removing
DECLARE stmt STATEMENT;


That works. Thanks, I'll get the hang of this eventually.

--
J. Moreno
Apr 27 '06 #10

P: n/a
J. Moreno wrote:
Serge Rielau <sr*****@ca.ibm.com> wrote:
J. Moreno wrote:
Serge Rielau <sr*****@ca.ibm.com> wrote: -snip- CREATE PROCEDURE p()
BEGIN
DECLARE var CHAR(1);
DECLARE txt VARCHAR(100);
DECLARE stmt STATEMENT;
DECLARE cur CURSOR WITH RETURN FOR stmt;
SET txt = 'SELECT * FROM SYSIBM.SYSDUMMY1 WHERE IBMREQD = ?';
PREPARE stmt FROM txt;
SET var = 'Y';
OPEN cur USING var;
END
Doesn't build. LINE 14 COL 17 UNSUPPORTED DATA TYPE DISTINCT TYPE
ENCOUNTERED IN SQL VARIABLE STMT

Off to do some more googling...

Try removing
DECLARE stmt STATEMENT;


That works. Thanks, I'll get the hang of this eventually.

Me too. I just learned why for DB2 for LUW that DECLARE is optional.
What we do for compatibility with the elder brother... :-)

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Apr 28 '06 #11

This discussion thread is closed

Replies have been disabled for this discussion.