Using variable for DBname in SELECT statement, within stored procedure

Hello,

I'm using DB2 v5r2 on iSeries AS/400.
Is it possible to use a variable as the database name in a select statement ?

For example something that would look like:
[...]
DECLARE v_total INT DEFAULT 0;
DECLARE v_mydb VARCVHAR(10) DEFAULT '';

SET v_mydb = 'DBNAME';

SELECT  COUNT(field)
INTO      v_total
FROM    [v_mydb].tablename
[...]

The reason for this is that I have 10+ databases identical from a structure standpoint, but different data. And based on a parameter to the stored procedure, I need to point to a different database, and I'd hate to have to use CASE / WHEN, and have to replicate the code inside each case statement...

Thanks !
LVL 7
naulivAsked:
Who is Participating?
 
Kent OlsenData Warehouse Architect / DBACommented:
Hi nauliv,

Sure.

Within the SP, declare a string variable:

declare   QueryStatement   varchar(2000);

Then build the query:

SET  QueryStatement = 'SELECT COUNT(field) INTO v_total FROM ' || v_mydb || '.tablename';

And then run it:

EXECUTE IMMEDIATE QueryStatement;



Kent
0
 
Kent OlsenData Warehouse Architect / DBACommented:
Hi nauliv,

Yes, you can do this.  But you have to build the query as a string and then EXECUTE or EXECUTE IMMEDIATE the string.  Note that you'll insert the correct database name into the query.


Good Luck!
Kent
0
 
naulivAuthor Commented:
Hello,

Thanks for your message. How can this can be done from within a stored procedure ?
Can you post an example of what the stored procedure code would look like ?

Thanks !
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
naulivAuthor Commented:
Kent,


How do you deal with return variables ?


Thanks !
0
 
Kent OlsenData Warehouse Architect / DBACommented:
Hi nauliv,

As long as vTotal is declared, I think that it should run fine.


Kent
0
 
naulivAuthor Commented:
Trying....
0
 
naulivAuthor Commented:
sorry didn't get a chance to test, but think it should work. thanks for your help, kent.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.