473,728 Members | 1,916 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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(s qltxt 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(S UBSTR(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(s trSQL);
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 10622
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(s qltxt 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(S UBSTR(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(s trSQL);
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
2334
by: Hursh | last post by:
Hi, I have written some stored procedures in SQL and these procedures return some value. I want these values to be captured by the ASP code. I am able to access the tables using ADO( recordsets ) but is there a way to pass data returned from stored procedures to vairables in ASP code.
1
4228
by: Harrie K. | last post by:
I want to make an Stored Procedure on a Oracle 7.x database that fills a resulset based on the values in an array: CREATE OR REPLACE PACKAGE PCK_SET IS TYPE rec_set IS RECORD ( type_id NUMBER , name VARCHAR2(30) )
1
2067
by: June Moore | last post by:
Hi, I would like to write a stored procedure that returns a resultset. e.g. select student_id, student_subject, subject_marks from student_results where student_class_no = ? The input parameter is student_class_no (see ? as per above SQL). The output is a resultset, with each result consisting of student_id,
3
16945
by: WGW | last post by:
Though I am a novice to MS SQL server (2000 I believe), I can do almost! everything I need. Maybe not efficiently, but usefully. However, I have a problem -- a complex query problem... I can create a parameter query in a stored procedure, but how do I use the result set of a parameter query in a select query (in the same or another sp)? In short, if a select query contains a result table that is generated as a parameter query, how do I...
2
2530
by: John Spiegel | last post by:
Hi all, Any suggestions on the best way to pass a set of values to a stored procedure that will use them in a query? Specifically, I want to call a stored procedure from .NET that takes a set of phone numbers and returns a resultset of records containing these numbers from a table. Is it possible to pass a dataset/XML or something that would be interpreted as a table? Or do I need to pass a string and convert it to a temporary table...
2
1791
by: Savas Ates | last post by:
I have a stored procedure below.. When I run it with a well parameter in query analyser 3 of select statements return me. (i named select statements 1,2,3) But in asp page when i call this procedure same as query analyser it returns me 2 select statements value? what is the problem ? CREATE PROCEDURE ST_25INDIRIM @sesid BIGINT AS
3
4240
by: Koen | last post by:
Hi, first of all, the machine setup server 1: - UDB2 7.2.5; COUNTRY=1, location = US, IBM1250 codepage - Locale: US English; Regional Settings: English; Keyboard: Dutch; decimal separator: . server 2: - UDB2 7.2.5 Client Tools - Websphere 4.0.5
4
4455
by: _link98 | last post by:
Problem: java ResultSet cursor from SQL/PL stored-procedure is FORWARD_ONLY. Is it possible to have ResultSet cursors from SQL/PL procedures to scroll forward and backwards? Perhaps I am missing something. Environment: client UDB 8.1 FP9a on Win32, server UDB 8.1 FP9a on Solaris 8 (64BIT). The JDK version on the WinXP client is
0
1566
by: av~ | last post by:
Hi All , I am trying to call a db2 stored proc with cursor open from my java code but it always returns me null results though it executes fine when I call the procedure from the command line Java code.................... CallableStatement stmt3 = EyeHibernateApp.getHibernateSession().connection().prepareCall("{call answers_select_id( ? )}"); stmt3.setString(1,20); stmt3.execute();
0
8904
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9424
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
9198
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8133
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6718
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
6013
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
1
3236
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2668
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2161
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.