Re: Executing Stored procedures with multiple Result sets

Jacob Salomon wrote:
>
> Mike Gallagher wrote:
>
> |||Are there any limitations in ODBC with respect to executing stored
> |||procedures. I am currently using Sybase/db-lib and I routinely
> |||execute stored procs that return multiple (different) result sets.
> |||I know Informix is limited to one result set per stored procedure.
> || ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
>
> Jacob Salomon responded:
>
> ||Whoa there, Mike! Please explain what you mean by a "result set".
> ||A stored procedure (in Informix) can return many rows of data and each
> ||row can consist of many columns. It does this by declaring its own
> ||cursor, FOREACH loop and the statement "RETURN... WITH RESUME". Next
> ||time the procedure is called, it starts executing from the statement
> ||following that RETURN.
> ||
> ||Is this the capability you are looking for?
>
> Mike Gallagher continued the thread in e-mail:
>
> |Thanks for the quick response.
> |
> |From within a single stored procedure
> |
> | select a,b,c from table_1
> |
> | select a,d,g,h,i from table_2
> |
> | select a,b,c,d,e,f,g,h,i,k from table_3
> |
> |Sybase considers a "result set" the results of a select statement.
> |There is no requirement that all columns from all of the selects be the
> |same data types
>
> Jake answered again:
>
> Mike,
>
> I leave the choice up to you, but for the benefit of the C.D.I family, I
> think you should post this response, now that your need is clearly
> stated and my workaround works (sort of).
>
> There you have'em; Informix does not allow a procedure to return a
> varying number of values. However, I think this is a useless feature.
> Consider a 4GL application fetching rows from the [prepared] "execute
> procedure" statement. It is expecting a N columns and has N variables
> set up to receive them. If the procedure returns or more than N values,
> the result is an error in the program. Even ESQL would have a tough
> time with this, unless you actually LIKE down & dirty dynamic SQL.
>
> However, if you *really* need such a "varying" result set, you can
> return nulls in place of the unfilled values. Like this procedure I
> just tried:
>
> create procedure varying()
> returning int, int, int, int; -- Always returns 4 values
>
> define lc smallint;
>
> for lc = 1 to 4
> if lc = 1
> then
> return 1,
> null, null, null
> with resume;
> elif lc = 2
> then
> return 1, 2,
> null, null
> with resume;
> elif lc = 3
> then
> return 1, 2, 3,
> null
> with resume;
> elif lc = 4
> then
> return 1, 2,3,4
> with resume;
> end if
> end for
> return;
> end procedure;
>
> I then went back into SQL and executed it. Here is the result:
>
> execute procedure varying();
>
> (expression) (expression) (expression) (expression)
>
> 1
> 1 2
> 1 2 3
> 1 2 3 4
>
> --
> -- Jake (Emulator of useless functionalities)
>
> +-----------------------------------------------------------+
> | Impeccable Logic: A thought process which successfully |
> | resists chicken bites |
> +-----------------------------------------------------------+

That works for the simple case but not for the complex or dynamic situation. Yes, we do often display information dynamically depending on what is in the database. Also, there are times that we do work in several temporary tables within a stored procedure and need to select out of the temporary tables. Just as an aside, Sybase does not require you to define the variables or their types when selecting out of a table.

e.g.
create procedure xyz
as

	select col_int, col_char, col_char2, col_float, col_datetime
	from
		my_table

go

Michael Gallagher
Open Link Financial
Email: mgallagh_at_olf.com
Phone: (516) 227-6600 x212 Received on Thu May 08 1997 - 00:00:00 CEST