set variables to be used in creation of result set.

[elliottjeff]

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.

[Knut Stolze]

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

[mandible]

thank you.

[J. Moreno]

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

[Serge Rielau]

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

[J. Moreno]

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

[Serge Rielau]

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

[J. Moreno]

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

[Serge Rielau]

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

[J. Moreno]

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

[Serge Rielau]

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