Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB Enhancement Requests and Suggestions » View Thread |
Messages 1 to 6 of 6 total |
Ability to use a StoredProce which returns data in a SELECT |
Sat, Sep 21 2013 8:49 AM | Permanent Link |
Adam Brett Orixa Systems | 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 |
Mon, Sep 23 2013 2:16 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Thu, Sep 26 2013 5:43 AM | Permanent Link |
Adam Brett Orixa Systems | 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 |
Thu, Nov 12 2015 9:50 AM | Permanent Link |
Gregory T Swain Zucchetti North America LLC | 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. . |
Fri, Nov 13 2015 7:17 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Fri, Nov 13 2015 3:28 PM | Permanent Link |
Gregory T Swain Zucchetti North America LLC | 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. |
This web page was last updated on Friday, April 19, 2024 at 07:09 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |