Passing a resultset from a nested Stored Procedure

[Twan Kennis]

Question: How do I pass a returning resultset from a nested Stored
Procedure (which opens a cursor including option "WITH RETURN TO
CALLER") as a returning resultset from it's own?

When I execute the top-level Stored Procedure, it executes succesfully,
but without any resultset.
================================================== ========
CREATE PROCEDURE sp_executesql(sqltxt CLOB(2M))
-- Executes given SQL-statement and returns resultset to caller

LANGUAGE SQL
MODIFIES SQL DATA
DYNAMIC RESULT SETS 1

BEGIN
DECLARE prefix VARCHAR(6);
DECLARE stmt CLOB(2M);

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
================================================== ========

When used straight from the SQL commandline, I get the result I asked
for, like

CALL sp_executesql('select * from ARTICLES') returns i.e. 100 article-records

Now I'd like to call this StoredProc from another StoredProc, like:

================================================== ========
CREATE PROCEDURE GetArticles()
LANGUAGE SQL
DYNAMIC RESULT SETS 1

BEGIN
DECLARE strSQL VARCHAR(255);
SET strSQL = 'select * from ARTICLES';
CALL sp_executesql(strSQL);
END
================================================== ========
CALL GetArticles()

it succesfully executes, but WITHOUT any result-set.
executing it once again, I get an error "Prepared statement STMT in
use." from which I understand the opened cursor remains still open.

Is there a way to get this expmaple working?

How can I store the resultset in a user-temporary table like on SQL
Server:

==============================
DECLARE @sql VARCHAR(255)
SET @sql = 'select * from ARTICLES'

INSERT INTO #temptable EXECUTE sp_executesql @sql

or

SELECT * FROM ARTICLES
INTO #temptable

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

Many thanks in advance.

Twan Kennis
SKB Vragenlijst Services
Amsterdam, The Netherlands

[Sean McKeough]

There are 2 options for result sets:

1) return to caller - this will pass the result set back up 1 level of
nesting
2) return to client - this will return the result set directly to the
application that invoked it

There's no way to use/pass result sets between nested procedures, _and_
have the client access them.

Twan Kennis wrote:

Question: How do I pass a returning resultset from a nested Stored
Procedure (which opens a cursor including option "WITH RETURN TO
CALLER") as a returning resultset from it's own?

When I execute the top-level Stored Procedure, it executes succesfully,
but without any resultset.
================================================== ========
CREATE PROCEDURE sp_executesql(sqltxt CLOB(2M))
-- Executes given SQL-statement and returns resultset to caller

LANGUAGE SQL
MODIFIES SQL DATA
DYNAMIC RESULT SETS 1

BEGIN
DECLARE prefix VARCHAR(6);
DECLARE stmt CLOB(2M);

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
================================================== ========

When used straight from the SQL commandline, I get the result I asked
for, like

CALL sp_executesql('select * from ARTICLES')
returns i.e. 100 article-records

Now I'd like to call this StoredProc from another StoredProc, like:

================================================== ========
CREATE PROCEDURE GetArticles()
LANGUAGE SQL
DYNAMIC RESULT SETS 1

BEGIN
DECLARE strSQL VARCHAR(255);
SET strSQL = 'select * from ARTICLES';
CALL sp_executesql(strSQL);
END
================================================== ========

CALL GetArticles()

it succesfully executes, but WITHOUT any result-set.
executing it once again, I get an error "Prepared statement STMT in
use." from which I understand the opened cursor remains still open.

Is there a way to get this expmaple working?

How can I store the resultset in a user-temporary table like on SQL
Server:

==============================
DECLARE @sql VARCHAR(255)
SET @sql = 'select * from ARTICLES'

INSERT INTO #temptable EXECUTE sp_executesql @sql

or

SELECT * FROM ARTICLES
INTO #temptable

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

Many thanks in advance.

Twan Kennis
SKB Vragenlijst Services
Amsterdam, The Netherlands

[Josh Tiefenbach]

Twan Kennis wrote:

Question: How do I pass a returning resultset from a nested Stored
Procedure (which opens a cursor including option "WITH RETURN TO
CALLER") as a returning resultset from it's own?

Look at the documentation for ASSOCIATE RESULT SET LOCATORS and
ALLOCATE CURSOR

jsoh