May 3, 2012 at 9:15 pm
I have a query that runs on 4 different databases and they are virtually identical. All the databases are on the same server.
Is there a way to dynamically pass the database as a parameter and add it to the query?
For example, if I have this now on each server in a SP:
SELECT *
FROM Users u
JOIN People p
ON u.UserID = p.UserID
I want to pass the database name as a parameter and change it to:
SELECT *
FROM Users u
JOIN Database1.dbo.People p
ON u.UserID = p.UserID
where Database1 could be Database1, Database2, Database3 etc.
I can do it using dynamic SQL but was wondering if it could be done by using the parameter directly.
Thanks,
Tom
May 4, 2012 at 12:58 am
I don't think you can do that without using Dynamic SQL. If you user the following code:
Declare @Tablename1 varchar(30) = 'Test'
Declare @Tablename2 varchar(30) = 'Test1'
Select * From @tablename1 as u JOIN @tablename2 as p ON u.Id = p.Id
The compiler considers "@Tablename1", "@Tablename2" as table variables and not temporary variables(parameters).
I tried a few other logic as well, but didn't come up with anything.
Finally, I had to say that I don't think you can do that without using Dynamic SQL.
It'll be great if some pros from SQL Server Central can prove me wrong and come up with something.....its always great to learn something new.
May 11, 2012 at 11:51 am
That was pretty much what I figured.
I can do the join to the database table to get the records that for each database by adding a DatabaseID column to the table but not for the Join of the actual table in each database.
Thanks,
Tom
May 11, 2012 at 12:41 pm
You could make a view with all 4 queries "union all"-ed together and add a derived column for the database name, that way you can just do your query and have
WHERE DBName = 'Database3'
There may be performance implications, but if that isn't of utmost concern, you can just abstract it out.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply