stored procedure with dynamic result set and execute immediate

[Frank Ploessel]

Hi,

I was trying the "execute immediate with result set on" statement.
But the following code gives an error "sp_test returned a result set with
a different schema than expected" in ASA 10.0.1.3662 if executed from ISQL:

create procedure sp_test()
begin
execute immediate with result set on 'select 1 as a, 2 as b from dummy'
end;
select * from sp_test()

I just do not see what I am doing wrong, as this is how I interpreted the
documentation about dynamic result sets and the execute immediate with
result sets.

Frank

[Glenn Paulley [iablog.sybase.com/paulley]]

When SELECTing from a stored procedure, the query optimizer must know
what the schema of the result set returned by the procedure looks like,
because it has to construct (at compile time) the rest of the data
structures that make up the statement. Hence there is no ability for the
query optimizer to handle dynamic result sets, or procedures that return
more than one result set.

If you specify the WITH clause on the procedure call in the query's FROM
clause, this tells the query optimizer what to expect, and your
example will work:

select * from sp_test() with (a int, b int)

Without a WITH clause, then the procedure's result set will be assumed
to be what is specified in the database's catalog, which is the
procedure's RESULT declaration (which is missing from your example).

Hope this clarifies things.

Glenn

--
Glenn Paulley
Director, Engineering (Query Processing)
Sybase iAnywhere

Blog: http://iablog.sybase.com/paulley

EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all

To Submit Bug Reports: http://case-express.sybase.com

SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288

Whitepapers, TechDocs, and bug fixes are all available through the
Sybase iAnywhere pages at
http://www.sybase.com/products/databasemanagement/sqlanywhere/technicalsupport

[Frank Ploessel]

Glenn,

Thank you for clarifying this. From your explanation I understood that the
select is the issue, not the dynamic result set. So I found that

call sp_test

works in ISQL, as does using the procedure as the source of a PowerBuilder
datawindow.

It was so simple. I was just thinking too complex.

Frank

[Glenn Paulley [iablog.sybase.com/paulley]]

Glad I could help. Fundamentally the problem is one of compile-time
construction versus run-time construction, which is hard to overcome if
the latter can be arbitrary.

Cheers

Glenn