Re: SQL Stored Procedure

James,

I won't say it can't be done, but it's going to be very, very hard to
do what you want.

It seems as if you want to provide your own STRSQL type application,
why? What's the business need, perhaps there is a better way.

If the caller can use SQL to call your procedure, why can't the caller
simply run the statement directly?
For a roll your own STRSQL application, an SQL stored procedure is
probably a poor choice in the first place.

In any event, as you've currently coded it, you're going about the
task completely wrong.

You can't CLOSE a cursor you want returned to a caller.
You also can't FETCH from the cursor you want returned, that's the caller's job.

HTH,
Charles





On Tue, Apr 28, 2009 at 9:18 AM, Salter, James <JSalter@xxxxxxxxxx> wrote:

Rob, I need to get back the return set for the row range that is
requested.

This would be in the order that the statement requests not RRN.

I need to fetch from row nBegin to nEnd.

Thanks.
James Salter
Systems Programmer
American Cast Iron Pipe Company
phone (205) 325-3033
fax      (205) 307-3833

from: rob@xxxxxxxxx
subject: Re: SQL Stored Procedure

More information please, like what are you going to do with this?
Return
the number of rows as a result set?

Rob Berendt
--
Group Dekko Services, LLC
Dept 01.073
Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com

From:
"Salter, James" <JSalter@xxxxxxxxxx>
To:
<midrange-l@xxxxxxxxxxxx>
Date:
04/27/2009 04:18 PM
Subject:
SQL Stored Procedure
Sent by:
midrange-l-bounces@xxxxxxxxxxxx


I am trying to create a stored procedure that accepts an SQL statement,
a FROM row #, & a TO row number.

If I could ask your help with the syntax on where to put the row logic
(or just the syntax in general) I would appreciate it.

CREATE PROCEDURE LIBRARY/PROCNAME
          (IN SQL_STATEMENT CHAR (2000),
           IN NFROM INT,
           IN NTO   INT)
           RESULT SETS 1
           LANGUAGE SQL
            BEGIN
                  DECLARE C1 DYNAMIC SCROLL CURSOR
                          WITH RETURN TO CALLER
                                 FOR SQL_STATEMENT;
                        OPEN C1;
                          FETCH FROM C1;
                        CLOSE C1;
           END

Thanks.
James Salter
Systems Programmer
American Cast Iron Pipe Company
phone (205) 325-3033
fax      (205) 307-3833




_________Confidentiality Notice_______________________
This e-mail and any files transmitted with it is
confidential and is intended solely for the use of
the individual(s) or entity(ies) to whom this e-mail
is addressed.  If you are not the intended recipient
or the person responsible for delivering the e-mail
to the intended recipient, be advised that you have
received this e-mail in error, and that any use,
disclosure, dissemination, forwarding, printing,
retention or copying of this e-mail is strictly
prohibited.  If you have received this e-mail in
error, please immediately return this e-mail to
the sender and delete the e-mail from your system.
Thank you.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.