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

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

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

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