Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB Public Beta Tests » View Thread |
Messages 1 to 4 of 4 total |
Where is the SELECT in stored procedures? |
Tue, Jan 23 2007 9:06 PM | Permanent Link |
Altaireon | I noticed SELECT is not supported in stored procedures. When I try to write a select statement in the stored procedure, I receive an error message stating that something other than the SELECT was
expected. From years of writing stored procedures with Oracle and SQL Server, I just can not imagine not being able to use a select statement in a stored procedure. Looking at your on-line manual for SQL/PSM, SELECT is not listed. Is this a future enhancement, an oversight, or is there something I should know about this? I am interested in being able to dynamically pass a value to a stored procedure so records can be selected based on the input parameter(s). The benefit being all of the SQL could reside in the database without having the SQL statements on the client. A View is not helpful here becuase I would have to select from the view and filter by columns. Some, albeit less, SQL would still be on the client. Thanks for your help. Tom Altaireon |
Wed, Jan 24 2007 11:01 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Tom,
<< I noticed SELECT is not supported in stored procedures. >> It is, but all DML and DDL in stored procedures is executed using dynamic SQL via the PREPARE, EXECUTE, and EXECUTE IMMEDIATE statements. << I am interested in being able to dynamically pass a value to a stored procedure so records can be selected based on the input parameter(s). The benefit being all of the SQL could reside in the database without having the SQL statements on the client. >> Here's an example of such a procedure. It returns the cursor to the calling application also, but that is optional. CREATE PROCEDURE CustomersByState(IN "State" CHAR(2) COLLATE ANSI_CI) BEGIN DECLARE Result CURSOR WITH RETURN FOR Stmt; PREPARE Stmt FROM 'SELECT * FROM Customer WHERE State=?'; OPEN Result USING State; -- Now you have a cursor open with the desired rows, you could do more processing here if necessary END -- Tim Young Elevate Software www.elevatesoft.com |
Wed, Jan 24 2007 12:18 PM | Permanent Link |
Altaireon | This does work and I do get what I am looking for. Where do I find docs to I can learn more about SQL/PSM and how to implemet stored procedures in ElevateDB.
Lastly, how can I view results of said stored procedure in ElevateDB. ALso, once I save the stored procedure, is there any way to go back and edit it. Thanks Tom Altaireon |
Wed, Jan 24 2007 6:18 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Tom,
<< This does work and I do get what I am looking for. Where do I find docs to I can learn more about SQL/PSM and how to implemet stored procedures in ElevateDB. >> The SQL/PSM documentation will be available online by this evening. I'm putting the final touches on it right now. The current documentation has the syntax for the SQL/PSM statements, but that's it. << Lastly, how can I view results of said stored procedure in ElevateDB. ALso, once I save the stored procedure, is there any way to go back and edit it. >> You currently have to drop the stored procedure and then re-create it, but to get the stored procedure definition, just use this query on your database: SELECT * FROM Information.Procedures The procedure definition will be in the Definition column. The information about the Information schema will also be available by tonight, and it has the structure of the Procedures table and all of the other tables in the Information schema. -- Tim Young Elevate Software www.elevatesoft.com |
This web page was last updated on Saturday, April 27, 2024 at 08:52 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |