Accessing Multiple databases from Stored procedure in SQL 2000

Hi ,

I have around 5 databases with same structure used to store data for different locations and services.

I have created an intermediate database which will have all the stored procedures to access data from above 5 databases.

My queries are common to all the databases. so,I would like to pass database name as an argument to my stored proc and execure query on specified database.

I tried this doing using "USE Databasename " Command. but it says ...We can not use "USE command " in stored proc or triggers. so ..what i did is ..

--

CREATE PROCEDURE TestDB_access(@.dbname varchar(100))
AS

BEGIN

DECLARE @.localDB varchar(100)

Set @.LocalDB=@.dbname

if @.LocalDB='XYZ'

EXEC ('USE XYZ ')
elseif @.LocalDB='ABC'

EXEC ('USE ABC')

Select * from Mytable

END

When I run this from my database , it gives me an error "unable to find table "mytable".

What is the best way to make my queries work with all the databases.

Thanks in advance

Venu Yankarla

Not sure what you are exactly trying to do but you can qualify a table with a database name and use it that way, however there is a limit in an SP of referring to 8 databases.

Try something like select * from Pubs..Authors where Pubs is the db name

|||

Thank you for your reply.

I can use the query "select * from Pubs..Authors where Pubs is the db name " if i know from which database i need to get the data.

In my case ... I want this dbname as a parameter to stored procedure. I can pass this to stored proc as parameter and bulid a string for SQL query and execute it. But I feel this as time consuming process as my queries will have atleast 5 joins in one query.

So , what I am looking for is .... Can we execute a query related to tables from a database (ABC or DEF or ETC) by using a stored proc in a database (XYZ)

Common thing in my case ..all databases has the same table structures but different data related to different locations.

in Query analyzer , we can execute queries on database (ABC or DEF) by staying in database (XYZ) using statement USE ABC and then execute SQL query.

USE ABC will change the database context and execute the query on "ABC" db tables.

Hope ..I am clear now..

Thanks

Venu yankarla

|||I think you will have to use sp_executesql for this