SQL Server 2000 - using multiple databases in a stored procedure

How to use data from multiple databases on the same server

[bhonda]

Hey,

Here's my situation.

I have a stored procedure. A lovely little thing it is too. I have a field, let's call it Department. In the database I'm using, it is simply an int; I'm using it as a foreign key.

The real department table, containing the primary key and the text description of each department, is in another database on the same server.

Now - simple question - how can I, from a stored procedure in my first database, get the text descriptions of each department from the other database? If the tables were in the same database, I'd just use an Inner Join, but is there any way to do this across databases?

Cheers - any advice will be welcome. If this just ain't possible, please just let me know so I don't spend ages trying to find out how to do it!

B

[syber]

All you have to do is reference the other database in your join.

For example:

SELECT mytable.deptno, t2.dept_description
FROM mytable JOIN otherdatabase..dept AS t2
ON mytable.deptno = t2.deptno

[bhonda]

Thanks!