Re: Multiple queries and stored procedures

[Mark Risman <mrisman@PAINEWEBBER.COM>]

Thanks, but does your stored procedure actually return two distinct result sets or select lists?  I know there are 1001 ways to work around it (e.g. write a "wrapper" stored procedure) but I was just wondering if there was an SQR way.

Thanks
- Mark

                -----Original Message-----
                From:   Ryszard Jedrychowski [mailto:Ryszard_Jedrychowski@CARDLINK.CO.NZ]
                Sent:   Wednesday, November 10, 1999 7:00 PM
                To:     Multiple recipients of list SQR-USERS
                Subject:        Re: Multiple queries and stored procedures

                Hi there,
                I'll give you an example:

                begin-procedure extract_records
                  let #page-count = 1

                ! std_driver_analysis is a stored procedure name
                  execute do=print_details std_driver_analysis $plan_id #run_no
                  into    &rp_level               int,
                          &trading_name  char(80),
                          &addr1                char(40),
                          &addr2                char(40),
                          &addr3                char(40),
                          &addr4                char(40),
                          &name               char(40),
                          &rep_date               datetime,
                          &old_acct_no    char(20),
                          &ccentre_cust_id         int,
                          &cost_centre_code        char(20),
                          &cust_id            int,
                          &card_no            char(19),
                          &driver              char(40),
                          &tot_val            money,
                          &txn_count             int,
                          &avg_txn_value             float,
                          &language_ind  char(1)

                  if &rp_level = 2
                    if #previous_level = 1
                      next-listing need=1
                      graphic                   (,2,127)        horz-line 10
                      print &txn_count              (+2,64,)        edit 999,999
                      print &tot_val                  (,80,)          edit 999,999,999.99
                      print &avg_txn_value              (,110,)         edit 999,999,999.99
                      next-listing need=1
                      graphic                   (,2,127)        horz-line 10

                      new-page

                      let #page-count = 1
                      let #previous_level = &rp_level
                    end-if
                  else
                    print 'No data available' (+1,,)          center
                  end-if
                end-procedure

                !-----------------------------------------------------------------------
                begin-procedure print_details
                  if &rp_level = 1
                    if #previous_level = 2 or #previous_level = 0
                       move &trading_name to $trading_name
                       move &addr1 to $addr1
                       move &addr2 to $addr2
                       move &addr3 to $addr3
                       move &addr4 to $addr4
                       move &name to $name
                       move &rep_date to $rep_date
                       move &old_acct_no to $old_acct_no
                       move &ccentre_cust_id to #ccentre_cust_id
                       move &cost_centre_code to $cost_centre_code
                       move '' to $previous_txn_dt

                       do language_support

                    end-if


                    move &card_no to $card_no
                    let $card_no = substr($card_no,12,6)

                    print $card_no                 (+1,3,)
                    print &driver                        (,16,40)
                    print &txn_count                  (,64,)     edit 999,999
                    print &tot_val                 (,80,)     edit 999,999,999.99
                    print &avg_txn_value                  (,110,)    edit 999,999,999.99

                     let #previous_level = &rp_level
                  end-if

                  if &rp_level = 2
                    next-listing need=1
                    graphic                            (,2,127)        horz-line 10
                    print &txn_count                  (+2,64,)        edit 999,999
                    print &tot_val                 (,80,)          edit 999,999,999.99
                    print &avg_txn_value                  (,110,)         edit 999,999,999.99
                    next-listing need=1
                    graphic                            (,2,127)        horz-line 10
                     let #previous_level = &rp_level
                     new-page

                    let #page-count = 1
                  end-if
                end-procedure


                Ryszard Jedrychowski






                mrisman@PAINEWEBBER.COM on 11/11/99 10:14:41 AM

                Please respond to SQR-USERS@list.iex.net

                To:   SQR-USERS@list.iex.net
                cc:    (bcc: Ryszard Jedrychowski/Cardlink/NZ)
                Subject:  Multiple queries and stored procedures




                Hi, I'm Mark and I'm new to this list.  I'd like to start off with a
                question:

                According to the SQRIBE SQR 4 Language Reference, under the Execute command
                 (I am using Sybase),
                 it says that if I try to execute a stored procedure that returns more than
                 one SELECT query,
                every record set beyond the first record set will be "ignored."  Is there a
                 way
                to retreive the the data that is in these "ignored" record sets?

                Thanks,
                - Mark