Very Computer

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

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!

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?
containing something like 'namesdb.dbo.customer'.

Quote:

> Thanks in advance, Mark

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.

hi mark,

Refer to following url to know downside using dynamic sql:

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

--
Vishal Parkar