How to join dynamic sql statement in variable with normal statement
- by Oliver
I have a quite complicated query which will by built up dynamically and is saved in a variable.
As second part i have another normal query and i'd like to make an inner join between these both.
To make it a little more easier here is a little example to illustrate my problem.
For this little example i used the AdventureWorks database.
Some query built up dynamically
(Yes, i know here is nothing dynamic here, cause it's just an example.)
DECLARE @query AS varchar(max) ;
set @query = '
select
HumanResources.Employee.EmployeeID
,HumanResources.Employee.LoginID
,HumanResources.Employee.Title
,HumanResources.EmployeeAddress.AddressID
from
HumanResources.Employee
inner join HumanResources.EmployeeAddress
on HumanResources.Employee.EmployeeID = HumanResources.EmployeeAddress.EmployeeID
;';
EXEC (@query);
The normal query i have
select
Person.Address.AddressID
,Person.Address.City
from
Person.Address
Maybe what i'd like to have but doesn't work
select
@query.*
,Addresses.City
from
@query as Employees
inner join
(
select
Person.Address.AddressID
,Person.Address.City
from
Person.Address
) as Addresses
on Employees.AddressID = Addresses.AddressID
