471,853 Members | 1,532 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,853 software developers and data experts.

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 10361
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
NeoPa
reply views Thread by NeoPa | last post: by
aboka
reply views Thread by aboka | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.