469,293 Members | 1,335 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,293 developers. It's quick & easy.

Passing a resultset from a nested Stored Procedure

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

Nov 12 '05 #1
2 10193
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

Nov 12 '05 #2
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

Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Hursh | last post: by
1 post views Thread by Harrie K. | last post: by
1 post views Thread by June Moore | last post: by
2 posts views Thread by John Spiegel | last post: by
2 posts views Thread by Savas Ates | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by harlem98 | last post: by
1 post views Thread by Geralt96 | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.