using a selected value in join tablename
-
does anyone know how or if I can accomplish the following?
SELECT s.ShowID, sd.ContactName, sd.ContactEmail, s.datasource
FROM show s
INNER JOIN [s.datasource].dbo.Showdetails sd
ON s.showid = sd.showid
where sd.SendNightlyEmail = 1
Basically I'm trying to join to a different sql database based on the datasource value that is returned in the select column... Is this possible?
-
in general, yes.
-
DECLARE
@strSQL nvarchar(1000)
,@dataSource varchar(100)
SET @dataSource = 'SomeDB'
SELECT @strSQL = 'SELECT s.ShowID, sd.ContactName, sd.ContactEmail, @dataSource
FROM show s
INNER JOIN [' + @dataSource + '].[dbo].[Showdetails] sd
ON s.showid = sd.showid
where sd.SendNightlyEmail = 1'
EXEC sp_executesql @strSQL
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. Selburg -
This might work better :
DECLARE
@strSQL nvarchar(1000)
,@dataSource varchar(100)
SET @dataSource = 'SomeDB'
SELECT @strSQL = 'SELECT s.ShowID, sd.ContactName, sd.ContactEmail, ' + @dataSource + '
FROM show s
INNER JOIN [' + @dataSource + '].[dbo].[Showdetails] sd
ON s.showid = sd.showid
where sd.SendNightlyEmail = 1'
EXEC sp_executesql @strSQL
-
Yep, that will probably work better, now I'll go stand in the corner for a few minutes. *GRIN*
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. Selburg -
If you insist, but just remember that I didn't put you there!
-
I'm well aware that it's all me... LOL
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. Selburg -
the solutions have me setting a datasource variable, what i'm trying to accomplish is that one of the selected columns (s.datasource) in the first table contains the database to be used for the join.
-
You would need to alter
SET @dataSource = 'SomeDB'
to
SET @datasource = (SELECT ColumnContainingDatabaseName
FROM TableContainingDatabaseName
WHERE CriteriaForDatabaseName
)
-------------------------------------------------------------------------
Normal chaos will be resumed as soon as possible. :crazy: -
the problem is that this query returns multiple rows, the join needs to be dynamic based on what the current row is returning in the first table... maybe this isn't possible and I need to handle it inside my programming language of choice.
-
Everything is possible in SQL. Only need some work.
Approach 1: Already U know how U can use Dynamic SQL for the purpose.
Approach 2: If U have to use another db frequently, why not U make a Linked Server and then use it as simple query.
-
I think I've already show how Dynamic SQL is not the answer in this case. This is a database on the same server, no need for linked servers. Once again, the query from the first table in the join returns multiple records, therefore i cannot just set a variable before i run the SQL.
-
Providing you do not exceed 4000 chars try
DECLARE @sql nvarchar(4000)
SET @strSQL = ''
SELECT @sql = @sql + 'SELECT s.ShowID,sd.ContactName,sd.ContactEmail,' + [dataSource] + ' FROM show s INNER JOIN [' + [dataSource] + '].[dbo].[Showdetails] sd ON s.showid=sd.showid WHERE sd.SendNightlyEmail=1;'
FROM show
EXEC sp_executesql @sql
Otherwise use a cursor
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply