Re: Stored Procedures
Nils Myklebust wrote:
>
> Dennis J Pimple <dennisp@informix.com> wrote:
>
> :Manoj Shroff wrote:
> :>
> :> Dear All,
> :>
> :> Is it possible to reference a variable in a select statement in
> :> a stored procedure, i.e
> :>
>
> :Afraid not. All the SQL has to be compiled at creation time, thus no
> :PREPARE in SPL, and thus no way to do "generic" SQL statements.
>
> But his example was as follows which sure should work (assuming he
> give a value to the TABLENAME variable somewhere in his first [snip]).
> If it doesn't whe would all have been out of luck.
> What doesn't work is as you say to prepare an arbitrary statement.
> There also is no way to do that in the spl language.
>
> manoj@future.dsc.dalsys.com (Manoj Shroff) wrote:
>
> : Is it possible to reference a variable in a select statement in
> :a stored procedure, i.e
>
> :create procedure test_one ()
> :define TABLENAME CHAR(20);
> :[snip]
> :select * from systables
> :where tabname = TABLENAME <----------------+
> :[snip] |
> :end procedure |
> : |
> : This is what I am talking about.
>
> Nils.Myklebust@idg.no
> NM Data AS, P.O.Box 9090 Gronland, N-0133 Oslo, Norway
> My opinions are those of my company
> The Informix FAQ is at http://www.iiug.org
Quite right. Thanks to you and Jonathan Lefler for pointing this out.
Jonathan's example that works is:
QUOTE:
Please consider:
CREATE PROCEDURE TableNumber(TableName CHAR(18)) RETURNING INTEGER;
DEFINE tabnum INTEGER;
FOREACH SELECT TabId INTO tabnum FROM SysTables WHERE TabName =
TableName
RETURN tabnum;
END FOREACH;
RETURN NULL;
END PROCEDURE;
This compiles and works for me under 7.21.UC1 OnLine on Solaris 2.4, and
should
work on any other version, I think.
END QUOTE
I was thinking of a more general question (and obviously didn't read the
example). You *can* pass in one or more parameters and use them in the
WHERE part of a select statement. What you *can't* do is use PREPARE in
a Stored procedure, so you can't have a table name (for instance) in a
variable and build a SELECT statement around it (SELECT * FROM
table_name_variable will *not* work).
//////////////// =======================================================
////////// // Dennis J. Pimple Informix Software, Inc.
////// / /// Principal Consultant 5299 DTC Blvd Suite 740
///// // //// dennisp@informix.com Englewood CO 80111
//// // /////
/// // ////// recept: 303-850-0210
// // /////// direct: 303-740-5611 Opinions expressed are mine,
/ /////////// fax: 303-843-6408 and do not necessarily
//////////////// http://www.informix.com reflect those of my employer