There may already be a way to do this which I don't know about. However it would be great to be able to declare a procedure and then include it in a SELECT, in the same way you can currently use a VIEW.

The ability to pass params into the procedure within the SELECT would be particularly powerful.

i.e. StoredProc(IN aDate DATE)

SELECT
*

FROM Table T
LEFT JOIN StoredProc(Current_date) S ON S.ID = T.ID

Adam,

<< There may already be a way to do this which I don't know about. However
it would be great to be able to declare a procedure and then include it in a
SELECT, in the same way you can currently use a VIEW.  >>

This is on the list, but hasn't been addressed yet.  The reason why it's
problematic is that the results of the stored procedure are dynamic (in EDB)
and not stored anywhere.  Therefore, the name resolution for the query
compiler has to be altered greatly in order to cope with the fact that such
procedures would have to be actually executed before their results could be
known, and it's a big change.

Tim Young
Elevate Software
www.elevatesoft.com

That does sound tough Tim.

I have found that I can generate SQL which is as efficient by using a nested SELECT in the JOIN of a main SELECT:

This achieves the same thing as having a dynamic procedure, as I can plus params in to each JOIN SELECT at the same time as the param in the main SELECT, but the result comes out right.

This is a fantastic feature of EDB, (maybe of SQL generally) which I have only just discovered ... it would be great to put a few examples of using it into documentation somewhere, as it makes a number of anaylitic processes much faster.

SELECT
 fields
FROM aTable
LEFT JOIN (SELECT somemorefields FROM otherTable WHERE :Param)
WHERE :Param

I'm in the same situation where this would be exceedingly helpful. I can not unfortunately make my procedure a single query or sub query either.

If I were developing on your DBS though and trying to tackle this problem I would try to create a Table type similar to those in MS or MY SQL so you could declare a table on the fly in memory. That would address the storage and you could also use it as a return type to select directly against then. I'm sure the implications of such a process would be big, but just and idea for you all from a fellow developer.

.

Gregory,

<< If I were developing on your DBS though and trying to tackle this problem I would try to create a Table type similar to those in MS or MY SQL so you could declare a table on the fly in memory. That would address the storage and you could also use it as a return type to select directly against then. I'm sure the implications of such a process would be big, but just and idea for you all from a fellow developer. >>

You can do something similar like that now yourself.  Just have the procedure create a temporary table with the desired result set using CREATE TEMPORARY TABLE AS...:

http://www.elevatesoft.com/manual?action=viewtopic&id=edb2sql&topic=CREATE_TABLE

(you can give it the same name as the procedure in order to avoid conflicts)

Then, after the procedure is executed, go ahead and execute your query using the procedure name (which is actually the temporary table name).  Afterwards, if desired, you can drop the temporary table.

Tim Young
Elevate Software
www.elevatesoft.com

Tim,
  This is exactly how I ended up solving the issue I was having.  It would be a nice feature to be able to SELECT right from the results of a SP though.

SELECT * from myStoredProc;

Where myStoredProc would return a result set. Just save us devs a few steps

Thanks.