Re: Stored procedure variables [1408]
searching "stored procedure variable dynamic" into google CDI I found
"AFAIK stored procedures do not do dynamic SQL - i.e.
you can't pass in a table name as a parameter and
have SPL build the queries for you. What you _can_
do is make your program create stored procedures on
the fly as soon as it knows the table name, I think
that one or two people have posted relevant examples
on this group recently."
"Check out the Dynamic SPL blade at www.iiug.org"
----- Original Message -----
From: "Schmitz, Ro...." <Rob.B.Schmitz@mail.sprint.com>
To: <ids@iiug.org>
Sent: Friday, June 20, 2003 9:34 PM
Subject: Stored procedure variables [1408]
> Hello Informix gurus,
>
> We have a question about stored procedures. We are running version 9.3 =
> of IDS on AIX 5.1. We would like to pass into a stored procedure the =
> name of a database that would be used in the SQL of the procedure. For =
> example:
>
> --------------------------------------------
>
> create procedure rob_proc (dbname char(18))
> returning smallint;>
> define isitthere smallint;
>
> select count(*) into isitthere from dbname:systables where => tabname=3D'tsk_task';
>
> return isitthere;
>
> end procedure;
>
> --------------------------------------------
>
> We would like to put the value stored in the parameter dbname in front =
> of the reference to systables; e.g., if we pass in 'rob' as the dbname, =
> how would we make the SQL look like=20
>
> select count(*) into isitthere from rob:systables where => tabname=3D'tsk_task';
>
> When we put 'dbname' in it (dbname:systables) the engine tries to find a =
> database named 'dbname'. We would like to have the value stored in =
> dbname inserted there. I guess the korn shell script equivalent of what =
> we are trying to do would be:
>
> select count(*) into isitthere from ${dbname}:systables where => tabname=3D'tsk_task';
>
> Does anyone know how to put the variable in so that it's value is =
> inserted? We've tried a colon (like ESQL/C), a dollar sign, a percent =
> sign, etc. We've checked manuals and reference books and still can't =
> figure it out.
>
> Any help would be appreciated. Thanks
>
> Rob Schmitz
> 913-345-6281
> Rob.B.Schmitz@mail.sprint.com
>
>
>
sending to informix-list