SQL Dynamic join on EXECUTE table column

Question

Not sure if this is ever going to be possible, but I'm trying to do a dynamic join on EXECUTE, but the execution string has to come from a table column.... i.e

select * 
from (
         select table1.theSql,table1.userid 
         from table1 
        ) as a
inner join (execute a.thesql) as b on a.userId=b.userid

Answer 1

The short answer is no, something like that (at least that simply) is not possible.

The longer answer is that the closest thing to what you are trying to do would be a cross apply. Cross applies allow you to execute a function (or something that could be written as a database function) on each row. One of the limitations of functions is that they cannot have any side effects (ie, updates, deletes, inserts). Since executing arbitrary sql could have a side effect, you cannot use it in a function and therefore can't use it in an apply statement.

Is there a way to get around this? Yes, but it isn't pretty. You are going to have to go with a dynamic sql solution. Here is something that might be a starting point. SQL Fiddle

Sample Schema

create table jigger
(
    id int,
    sql varchar(500)
)

insert into jigger values (1, 'select * from otherStuff')
insert into jigger values (2, 'select * from otherStuff where jigger_id = 2')

create table otherStuff
(
    id int,
    jigger_id int,
    name varchar(10)
)

insert into otherStuff values (1, 1, 'thing 1')
insert into otherStuff values (2, 2, 'thing 2')
insert into otherStuff values (3, 3, 'thing 3')

SQL that Executes the SQL in the tables

declare @innerSql varchar(8000)

set @innerSql = stuff((select ' UNION ALL select ' + cast(j.id as varchar(500)) + ' as jiggerSourceId, * from (' + j.sql + ') isql' + cast(j.id as varchar(500))
                        from jigger j
                        for xml path ('')), 1, 11, '')

declare @sql varchar(8000)

set @sql = 'select * from jigger j inner join (' + @innerSql + ') x on x.jiggerSourceId = j.id'

exec (@sql)