Stored Procedure Parameter
Hello
All,
I received a question from one of our developers regarding a parameter passed
into a stored procedure. This particular application has one main database and
several hundred other databases per instance. The stored procedure will reside
in the main database. When they call the stored procedure, they want to be
able to pass in the name of one of the other databases as a parameter. For
example, if they pass in the database name and store it into a parameter named
dbase_name, how would they reference that in a statement? If they say
select * from dbase_name:table_name
the stored procedure looks for a database named dbase_name rather than
resolving the value stored in the parameter. Does anyone know how they would
go about this?
System Information:
AIX 5.2.0.0
IDS 7.31.UD6W8
Thanks
Rob Schmitz
Two solutions:
1. With a REALLY big CASE statement:
case dbname_arg
when "Database_no_1" THEN
FOREACH "SELECT ... FROM Database_no_1:table_1..."
INTO lcl_var_1, ...
BEGIN
RETURN lcl_var_1, ... WITH RESUME;
END
when "Database_no_2" THEN
...
end case
2. If you download the Dynamic SQL Datablade from the IIUG Software Repository
you CAN use dynamic SQL to build a query using a string and the passed in
databasename, prepare it, declare a cursor, etc.
Art S. Kagel
----- Original Message -----
From: Ro.... Schmitz <Rob.B.Schmitz@mail.sprint.com>
At: 12/21 13:06
> Hello All,
>
> I received a question from one of our developers regarding a parameter passed
> into a stored procedure. This particular application has one main database
and
> several hundred other databases per instance. The stored procedure will
reside
> in the main database. When they call the stored procedure, they want to be
able
> to pass in the name of one of the other databases as a parameter. For
example,
> if they pass in the database name and store it into a parameter named
> dbase_name, how would they reference that in a statement? If they say
>
> select * from dbase_name:table_name>
> the stored procedure looks for a database named dbase_name rather than
resolving
> the value stored in the parameter. Does anyone know how they would go about
> this?
>
> System Information:
>
> AIX 5.2.0.0
> IDS 7.31.UD6W8
>
> Thanks
>
> Rob Schmitz