?
Solved

Using variable for DBname in SELECT statement, within stored procedure

Posted on 2006-05-18
9
Medium Priority
?
894 Views
Last Modified: 2008-02-01
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 !
0
Comment
Question by:nauliv
  • 4
  • 3
7 Comments
 
LVL 46

Expert Comment

by:Kent Olsen
ID: 16714257
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
 
LVL 7

Author Comment

by:nauliv
ID: 16714433
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
 
LVL 46

Accepted Solution

by:
Kent Olsen earned 500 total points
ID: 16714506
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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
LVL 7

Author Comment

by:nauliv
ID: 16714536
Kent,


How do you deal with return variables ?


Thanks !
0
 
LVL 46

Expert Comment

by:Kent Olsen
ID: 16714561
Hi nauliv,

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


Kent
0
 
LVL 7

Author Comment

by:nauliv
ID: 16714599
Trying....
0
 
LVL 7

Author Comment

by:nauliv
ID: 16915381
sorry didn't get a chance to test, but think it should work. thanks for your help, kent.
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Kernel Data Recovery is a renowned Data Recovery solution provider which offers wide range of softwares for both enterprise and home users with its cost-effective solutions. Let's have a quick overview of the journey and data recovery tools range he…

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question