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

execute a new string comprising the above interpolated with the string in theSql ? — Alex K.
– Alex K., Commented Jun 23, 2015 at 15:27
Does your dynamic SQL always return a result set that has a userid? Is the userid in table1 unique or can you have multiple rows per userid? — Jason Whitish
– Jason Whitish, Commented Jun 23, 2015 at 15:34

Becuzz · Accepted Answer · 2015-06-23 16:47:41Z

2

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)

answered Jun 23, 2015 at 16:47

Becuzz

6,86728 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

pelfed Over a year ago

looks interesting - tried to have a quick go at this put the for xml path ('')), 1, 11, '') seems to cause bad syntax?

Becuzz Over a year ago

Are you missing a parenthesis somewhere? Or the call to stuff? That part is meant to clear out the first UNION ALL you would get when mashing all that sql together.

pelfed Over a year ago

Its erroring on escape chars - example : contact_record.Customer = 1 

Collectives™ on Stack Overflow

SQL Dynamic join on EXECUTE table column

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related