Re: SQL Stored Procedure

[Charles Wilt <charles.wilt@xxxxxxxxx>]

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.