EXECUTE SQL-string from StoredProc

[Twan Kennis]

How can I execute a dynamically constructed SQL-SELECT-string within a
Stored Procedure on DB2, like the "sp_executesql"-command on SQL Server?

From the DB2 Information Center I got the following information:
"The EXECUTE-command requires a statement-name which must identify a
statement that was previously prepared, and cannot be a SELECT
statement."
http://publib.boulder.ibm.com/infoce...n/r0000948.htm
Thanx in advance,

Twan Kennis
SKB Vragenlijst Services
Amsterdam, The Netherlands

[Serge Rielau]

Twan Kennis wrote:

How can I execute a dynamically constructed SQL-SELECT-string within a
Stored Procedure on DB2, like the "sp_executesql"-command on SQL Server?

db2 -td@

CREATE PROCEDURE sp_executesql(sqltxt CLOB(2M))
MODIFIES SQL DATA
DYNAMIC RESULT SETS 1
BEGIN
DECLARE prefix VARCHAR(6);
DECLARE stmt STATEMENT;
PREPARE stmt FROM sqltxt;
SET prefix = UPPER(VARCHAR(SUBSTR(sqltxt, 1, 6)));
IF prefix = 'SELECT' OR
prefix = 'VALUES' OR
SUBSTR(prefix, 1, 4) = 'WITH'
THEN
BEGIN
DECLARE res CURSOR WITH RETURN TO CALLER
FOR stmt;
OPEN res;
END;
ELSE
EXECUTE stmt;
END IF;
END
@

CALL sp_executesql('CREATE TABLE T(c1 INT)')
@
CALL sp_executesql('INSERT INTO T VALUES 5, 6, 7')
@
CALL sp_executesql('SELECT * FROM T')
@

Enjoy
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Twan Kennis]

Dear Serge,

It works; thank you very much!!!
(Besides the STATEMENT-type was an unknown UDT, but that was a minor
problem to fix.)

Twan Kennis

==================================================

"Serge Rielau" <sr*****@ca.ibm.com> wrote in message
news:3c*************@individual.net...

Twan Kennis wrote:

How can I execute a dynamically constructed SQL-SELECT-string within a Stored Procedure on DB2, like the "sp_executesql"-command on SQL

Server?
db2 -td@

CREATE PROCEDURE sp_executesql(sqltxt CLOB(2M))
MODIFIES SQL DATA
DYNAMIC RESULT SETS 1
BEGIN
DECLARE prefix VARCHAR(6);
DECLARE stmt STATEMENT;
PREPARE stmt FROM sqltxt;
SET prefix = UPPER(VARCHAR(SUBSTR(sqltxt, 1, 6)));
IF prefix = 'SELECT' OR
prefix = 'VALUES' OR
SUBSTR(prefix, 1, 4) = 'WITH'
THEN
BEGIN
DECLARE res CURSOR WITH RETURN TO CALLER
FOR stmt;
OPEN res;
END;
ELSE
EXECUTE stmt;
END IF;
END
@

CALL sp_executesql('CREATE TABLE T(c1 INT)')
@
CALL sp_executesql('INSERT INTO T VALUES 5, 6, 7')
@
CALL sp_executesql('SELECT * FROM T')
@

Enjoy
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Serge Rielau]

Twan Kennis wrote:

Dear Serge,

It works; thank you very much!!!
(Besides the STATEMENT-type was an unknown UDT, but that was a minor
problem to fix.)

That's odd. I actually ran this script on FP8.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab