Re[3]: Processing results fro a stored procedure

[gadiy@sun4.miti.com]

     What's your version of the SQR manual? The 2.5 version has the EXECUTE 
     command described on page 4-59. If you don't have it you should 
     certainly get a copy.
     
     The execute command goes like that:
     
     EXECUTE
     
     Executes a stored procedure. (EXECUTE is restricted to the Sybase and 
     Ingres versions of SQR.)
     
     Syntax
     
     EXECUTE  [ -Cnn ]  [ ON-ERROR= procedure [ ( arg1 [, argi ] ... ) ]
     [ DO= procedure [ ( arg1 [, argi ] ... ) ]  [ @#status_var= ]
     stored_procedure_name
     [ [ @param= ]  { any_var | _lit }  [ OUT[PUT] ] ]  [, ...]
     [ INTO  any_col  data_type [ ( length_int_lit ) ] ]  [, ...]
     [ WITH  RECOMPILE ]
     
     The syntax of this command roughly follows that of the Sybase 
     Transact-SQL EXECUTE command, with the exception of the three optional 
     arguments and the INTO argument.
     
     Arguments
     
     -Cnn - Specifies the logical connection number.
     ON-ERROR - Declares a procedure to execute if an error occurs. If 
     ON-ERROR is omitted and an error occurs, SQR halts with an error 
     message. For severe errors (for example, passing too few arguments) 
     SQR halts, even if an error procedure is specified.
        You can optionally specify arguments to be passed to the ON-ERROR   
        procedure. Arguments can be any variable, column, or literal.
     DO - Specifies a procedure to execute for each row selected in the 
     query. Processing continues until all rows have been retrieved.
     You can optionally specify arguments to be passed to the procedure.  
     Arguments can be any variable, column, or literal.
     @#status_variable -  Returns the procedure's status in the specified 
     numeric variable. The status is returned only after selected rows are 
     retrieved.
     stored_procedure_name -  Names the stored procedure to execute.
     @param -   Names the parameter to pass to the stored procedure. 
     Parameters can be passed with or without names. If used without names, 
     they must be listed in the same sequence as defined in the stored 
     procedure.
     any_var | _lit -  Specifies the value passed to the stored procedure. 
     It can be a string or numeric variable, a previously selected column, 
     a numeric literal, or a string literal.
     OUT[PUT] -  Indicates that the parameter will receive a value from the 
     stored procedure. The parameter  must be a string or numeric SQR 
     variable. Output parameters receive their values only after rows 
     selected have been retrieved. If multiple output parameters 
     are specified, they must be in the same sequence as defined in the 
     stored procedure.
     INTO - Indicates where to store rows that are retrieved from the 
     stored procedure's SELECT statement. The INTO argument contains the 
     names of the columns with data types and lengths (if needed). The 
     columns must be specified in the same sequence and match the data type 
     used in the stored procedure's SELECT statement. If the stored 
     procedure contains more than one SELECT query, only the first query is 
     described with the INTO argument. Rows from subsequent queries are 
     ignored.
     WITH RECOMPILE - Causes the query to recompile each time it is 
     executed rather than using the plan stored with the procedure. 
     Normally, this is not required or recommended.
     
     Description
     
     The stored procedure must contain a SELECT statement, and the EXECUTE 
     command must contain an INTO argument. If no DO procedure is specified 
     and the stored procedure specified in stored_procedure_name 
     nevertheless selects one or more rows and an INTO argument is 
     specified, EXECUTE retrieves the first row only, ignoring the 
     remainder of the rows. This is useful for queries returning a single 
     row.
     
     Examples
     
     In the following example, the stored procedure get_total is invoked 
     with two parameters: a string literal and a string variable. The 
     result from the stored procedure is stored in the variable #total.
     
     execute get_total 'S. Q. Reporter' $State #Total Output 
     
     In the following example, the stored procedure get_products is invoked 
     with two parameters. The stored procedure selects data into four 
     &columns. The SQR procedure print_products is called for each row 
     retrieved. The return status from the stored procedure is placed in 
     the variable #proc_return_status:
     
     execute do=print_products
        @#proc_return_status=
        get_products
        @prodcode=&code, @max=#maximum
        INTO &prod_code   int,
                &description char (45),
                &discount    float,
                &restock     char
     
     begin-procedure print_products
     print &prod_code        (+1,1)
     print &description      (+5,45)
     print &discount (+5) edit 99.99
     print &restock          (+5) match Y 0 5 Yes N 0 5 No
     end-procedure


     Gadi.

______________________________ Reply Separator _________________________________
Subject: Re[2]: Processing results fro a stored procedure
Author:  usa.net!sqr-users@netcomsv.netcom.com at Internet
Date:    11/3/94 1:23 PM


I did not specify how I was running SQR. I am running it from windows version 
2.5 using sybase database. I will also be runnig it from SunOS using sybase.
     
I had heard that to get rows back you use EXEC but the manual I have does not 
have this documented. Could someone send me the syntax with perhaps an example 
program. 
     
     
Thanks 
     
Robert Voysey