Is there a more ideal way to query data from another database using SQL Server 2005?
I have a stored procedure which开发者_C百科 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?
You can use
OPENQUERY or OPENROWSET
精彩评论