Using a variable to specify database to SELECT from

Using a variable to specify database to SELECT from

Post by Mark Paulso » Wed, 03 Mar 2004 04:06:05



Hello,

I am writing a stored procedure that will be gathering data from two very, similar databases. Where possible I would like to write one set of code and pass it parameters in order to specify the database name to use. I've discovered that the USE <database_name> statement will not work in stored procedures. I've tried every way I can think of to use a variable as part of the FROM clause in a SELECT to no avail. Is something like the following somehow possible?

SELECT name,address


Thanks in advance, Mark

 
 
 

Using a variable to specify database to SELECT from

Post by Stephen Hendrick » Wed, 03 Mar 2004 04:30:13


Short answer...No, you can't do this.

It can be approximated using:

  1) Dynamic SQL
  2) IF-THEN construct

HTH

=======================================
Everyone here speaks SQL; some are more fluent, others less.  When
describing your SQL object (table, etc.), do so in the language that we
all understand - SQL, not English.  It makes it easier to understand
your issue and makes it more likely that you will get the assistance
that you are asking for.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 
 
 

Using a variable to specify database to SELECT from

Post by Simo » Wed, 03 Mar 2004 04:31:50


You can use dynamic SQL to apply the USE and the SELECT

Assuming that the proc just SELECTs and does nothing else
it could be something like


AS




GO


Quote:> Hello,

> I am writing a stored procedure that will be gathering data from two very,

similar databases. Where possible I would like to write one set of code and
pass it parameters in order to specify the database name to use. I've
discovered that the USE <database_name> statement will not work in stored
procedures. I've tried every way I can think of to use a variable as part of
the FROM clause in a SELECT to no avail. Is something like the following
somehow possible?

> SELECT name,address



containing something like 'namesdb.dbo.customer'.
Quote:

> Thanks in advance, Mark

 
 
 

Using a variable to specify database to SELECT from

Post by Roha » Wed, 03 Mar 2004 05:46:11


I agree with the use of dynamic SQL or if-then.

Both has disadvantages. Dynamic SQL requires recompiling, etc. If-then could result in possibly inefficient plan generated. Since you know the name of the database you want to call, you can create equavalent stored procedures in each database and call the appropriate one, eg exec db1.dbo.sp1, exec db2.dbo.sp1.

You can also create the stored proc in the master database and use the undocumented "sp_MS_makesystemobject" to flag it as a system object. (see http://www.winnetmag.com/SQLServer/Article/ArticleID/41044/41044.html)

Nevertheless, I still believe that dynamic SQL or if-then should be used unless its not practical to do so.

 
 
 

Using a variable to specify database to SELECT from

Post by Vishal Parka » Wed, 03 Mar 2004 19:01:31


hi mark,

Refer to following url to know downside using dynamic sql:

http://www.algonet.se/~sommar/dynamic_sql.html

--
Vishal Parkar

 
 
 

1. select statement using :variable as selected item

|> I can do this:
|>
|> exec sql repeated select attr1, attr2, 10, 0, attr4
|> into <list_here>
|> from tablename
|>
|> but can I do this:
|>
|> EXEC SQL BEGIN DECLARE SECTION;
|> long w_var1;
|> long w_var2;
|> EXEC SQL END DECLARE SECTION;
|>
|> w_var1 = 10;
|> w_var2 = 0
|>
|> exec sql repeated select attr1, attr2, :w_var1, :w_var2, attr4
|> into <list_here>
|> from tablename
|>
|> Regards,
|>
|> Brian A. Henk                     Did he say blessed are the cheesemakers?
|>
|>
|>

Yes. This can be done.

Regards,

Brian A. Henk                     Did he say blessed are the cheesemakers?

2. creating an alias for a object

3. selecting specified rows from a view using rownum

4. Credit cards

5. Specified SQL server not found in SP with SELECT Using LINKED SERVER

6. How can I debug my stored procedures?

7. ORDER BY items must appear in the select list if SELECT DISTINCT is specified

8. create table question (interMedia)

9. question about using variables in a select statement

10. Using SELECT @variable = in a dynamic SQL statement

11. Problems with using variables in SELECT

12. Using a Variable in a Select Statement in ASP

13. Using a Variable as Column name in Select statement in a Cursor