Is there a more ideal way to query data from another database using SQL Server 2005?

Question

I have a stored procedure which gets data from another database on the same server.

I will not be hard-coding which database to use, instead this will be configurable. Is this possible without dynamic sql?

The best solution I could come up with so far is to first run a stored procedure which uses dynamic sql to generate a bunch of views. I can then select data from these views to avoid using dynamic SQL for everything.

Example:

DECLARE 
    @databaseName nvarchar(max),
    @sql          nvarchar(max)

-- Get this value from a configuration table
SET @databaseName = 'TestDatabase'

IF EXISTS(SELECT NULL FROM dbo.SysObjects WHERE [Name] = 'TestView')
    DROP VIEW dbo.TestView

SET @sql = 'CREATE VIEW dbo.TestView AS SELECT * FROM ' + @databaseName +'.dbo.TestTable'

EXEC (@sql)

--I can now select from TestView using regular query.

I'm guessing that I'm going about this the wrong way. Is there a better way to do this?

Answer 1

You can use

OPENQUERY or OPENROWSET