How to join dynamic sql statement in variable with normal statement

Question

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 there 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

Solution 1

Use temp tables & have the records dumped into it (from the dynamic query) & use the temp table to join with the static query that you have.

set @query = 'CREATE table #myTempTable AS
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);

And then

select
    Employees.*
    ,Addresses.City
from
    #myTempTable as Employees
    inner join
    (
        select
            Person.Address.AddressID
            ,Person.Address.City
        from
            Person.Address
    ) as Addresses
    on Employees.AddressID = Addresses.AddressID

Solution 2

You might be on the right track, but as long as you trust the source of the parameter and not exposed for SQL-Injection, you might just have to alter your select when building your @query like:

parameter to your function  '@YourAlternateTableParm'

DECLARE @query AS varchar(max) ; 

set @query = 'select ' + @YourAlternateTableParm 
  + '.*, Addresses.City
from ' + @YourAlternateTableParm
  + ' as Employees 
    inner join 
    ( ..... '

This way, like you were building your original string, you are building the actual value of the parameter to a function/procedure call with the table name desired to represent your "Employees" file into the string, then you execute that. SQL is not dynamically interpretting the @query inline the way you were trying to do.

Comments

[Oliver]

yes, this is a solution i already thought of, but the statement is already quite big and i think if i take this approach i will hit the varchar(max) range. Also it leads to further hard links where later nobody is able to decompose the statement to understand what it is doing.

[Oliver]

i think the temp table is the right approach in my case. thanks for the tip.

[DRapp]

then split your string into multiple query parts... say 1 or 2 @vars for the select fields, and a 3rd for the from/where criteria... then EXEC( var1 + var2 + var3 )

[Mohsen]

I wanted to use this approach, but in ETL tools like Pentaho the temp tables like this example makes bugs during ETL.