stored procedure for different databases

Can I use a stored procedure to select records from different data开发者_StackOverflowbases (in SQL)? I have a table from each database and I want to join them.

Thanks.

Yes, as long as you have permissions. You can also do cross server if you have a Linked Server setup.

Cross DB Example:

SELECT * FROM localTable as lt
INNER JOIN otherDB.dbo.OtherTable as ot
ON lt.ID = ot.id

Cross Server example (assumes you have created the linked server):

SELECT * FROM localTable as lt
INNER JOIN ServerName.otherDB.dbo.OtherTable as ot
ON lt.ID = ot.id

Yes. Just use 3 part naming syntax database_name.schema_name.table_name.

You also need to either enable cross database ownership chaining. (Not recommended) or use this approach.

Use the fully qualified table name. e.g, If you are in database ABC and want to access table table in database XYZ, access it as XYZ.dbo.table (It could be dbo or something else depending on the owner of the table. Usually it is dbo)

Try something like the following:

SELECT a.userID, b.usersFirstName 
FROM databaseA.dbo.TableA a 
INNER JOIN database B.dbo.TableB b ON a.userID = b.userID

Source: http://forums.asp.net/t/1254974.aspx

One other technique that works better in some environments is to define a synonym for the remote table. This lets you have a local reference that points to the remote object. Particularly useful if you are in an environment where you have to migrate things and the relative locations may not be the same from one environment to the next. Here is a quick overview:

http://blog.sqlauthority.com/2008/01/07/sql-server-2005-introduction-and-explanation-to-synonym-helpful-t-sql-feature-for-developer/