Is is possible to Dynamic join on EXECUTE table column

Question

Is is possible to Dynamic join on EXECUTE table column

pjn

SSC Enthusiast

Points: 191
More actions
June 23, 2015 at 9:37 am

#319393

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

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286573 More actions · Answer 1

pjn (6/23/2015)
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

This will never work like that. You can't join to an exec statement. I can't even begin to offer a suggestion from this. Can you post the ddl and some sample data?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

Simple answer, no. Can you explain in greater detail what you are trying to accomplish?

pjn SSC Enthusiast Points: 191 More actions · Answer 3

Basically I am try to cut down some processing time.

Currently in its simplistic form:

sql1 = "select table1.theSql,table1.userid from table1"

loop through sql1.resultset

sq12 = sql1.theSql+' userid = sql1.userid'

do something with sql2.resultset

end loop

I'm just trying to get the best way to cut the time down and was playing with the idea I had initally to somehow inline this in SQL.

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 4

pjn (6/23/2015)
Basically I am try to cut down some processing time.
Currently in its simplistic form:
sql1 = "select table1.theSql,table1.userid from table1"
loop through sql1.resultset
sq12 = sql1.theSql+' userid = sql1.userid'
do something with sql2.resultset
end loop
I'm just trying to get the best way to cut the time down and was playing with the idea I had initally to somehow inline this in SQL.

Further on Lynn's and Sean's reply, can you detail WHAT you are trying to do without the HOW?

😎

pjn SSC Enthusiast Points: 191 More actions · Answer 5

Eirikur Eiriksson (6/23/2015)
pjn (6/23/2015)
Basically I am try to cut down some processing time.
Currently in its simplistic form:
sql1 = "select table1.theSql,table1.userid from table1"
loop through sql1.resultset
sq12 = sql1.theSql+' userid = sql1.userid'
do something with sql2.resultset
end loop
I'm just trying to get the best way to cut the time down and was playing with the idea I had initally to somehow inline this in SQL.
Further on Lynn's and Sean's reply, can you detail WHAT you are trying to do without the HOW?
😎

Not really much else I can say to add any detail. The answer is no, so thanks

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 6

pjn (6/23/2015)
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

Can you provide some examples of the string to execute?

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

Ed Wagner SSC Guru Points: 287024 More actions · Answer 7

pjn (6/23/2015)
Eirikur Eiriksson (6/23/2015)
pjn (6/23/2015)
Basically I am try to cut down some processing time.
Currently in its simplistic form:
sql1 = "select table1.theSql,table1.userid from table1"
loop through sql1.resultset
sq12 = sql1.theSql+' userid = sql1.userid'
do something with sql2.resultset
end loop
I'm just trying to get the best way to cut the time down and was playing with the idea I had initally to somehow inline this in SQL.
Further on Lynn's and Sean's reply, can you detail WHAT you are trying to do without the HOW?
😎
Not really much else I can say to add any detail. The answer is no, so thanks

While the answer to your direct question is no, there might be a way of accomplishing what you want.

So the question becomes what Lynn and Eirikur posted: What are you trying to accomplish?

Without knowing what you're trying to do, there's no way anyone will be able to help you figure it out.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 8

Sean Lange (6/23/2015)
pjn (6/23/2015)
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
This will never work like that. You can't join to an exec statement. I can't even begin to offer a suggestion from this. Can you post the ddl and some sample data?

Hmmmm.... maybe you can. Call the proc from OpenRowset.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sean Lange SSC Guru Points: 286573 More actions · Answer 9

pjn (6/23/2015)
Eirikur Eiriksson (6/23/2015)
pjn (6/23/2015)
Basically I am try to cut down some processing time.
Currently in its simplistic form:
sql1 = "select table1.theSql,table1.userid from table1"
loop through sql1.resultset
sq12 = sql1.theSql+' userid = sql1.userid'
do something with sql2.resultset
end loop
I'm just trying to get the best way to cut the time down and was playing with the idea I had initally to somehow inline this in SQL.
Further on Lynn's and Sean's reply, can you detail WHAT you are trying to do without the HOW?
😎
Not really much else I can say to add any detail. The answer is no, so thanks

So you need help with some technical implementation but you are unwilling or unable to offer any details about what you are trying to do? You realize this is like taking your car to the mechanic and telling them it needs to be fixed but don't give them any information about what is wrong. Best of luck. I truly hope you can find a solution to whatever it is you are trying to do.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

pjn SSC Enthusiast Points: 191 More actions · Answer 10

seriously? any need for that?

I asked a question, I accepted it, its fine, I wanted to know it something could be done (as in initial question), the answer to which was no. I have another solution which works fine now. Why the need to troll?

Sean Lange SSC Guru Points: 286573 More actions · Answer 11

pjn (6/23/2015)
seriously? any need for that?
I asked a question, I accepted it, its fine, I wanted to know it something could be done (as in initial question), the answer to which was no. I have another solution which works fine now. Why the need to troll?

I was not trolling. Not even close. This site functions a little differently than many other online forums. We actually try to help people find the best solution to an issue. Your approach sounds like it overly complicated to me and several others. We were asking for details so we could help you find a better approach to a problem that you were struggling with. Your response to Eirikur was very snarky.

What you are/were trying is potentially possible given some environment settings as eluded by Jeff Moden. Most of us are trying to get a real handle on your issue to help you with what seems a very strange and overly complicated approach to a problem. Most of the regulars around are actually pretty good at helping people figure out a better approach. I hope you can come back next time you have an issue. Maybe that time you will be able to share enough details for us to help you find an excellent approach instead of one that simply works.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 12

Sean Lange (6/23/2015)
pjn (6/23/2015)
seriously? any need for that?
I asked a question, I accepted it, its fine, I wanted to know it something could be done (as in initial question), the answer to which was no. I have another solution which works fine now. Why the need to troll?
I was not trolling. Not even close. This site functions a little differently than many other online forums. We actually try to help people find the best solution to an issue. Your approach sounds like it overly complicated to me and several others. We were asking for details so we could help you find a better approach to a problem that you were struggling with. Your response to Eirikur was very snarky.
What you are/were trying is potentially possible given some environment settings as eluded by Jeff Moden. Most of us are trying to get a real handle on your issue to help you with what seems a very strange and overly complicated approach to a problem. Most of the regulars around are actually pretty good at helping people figure out a better approach. I hope you can come back next time you have an issue. Maybe that time you will be able to share enough details for us to help you find an excellent approach instead of one that simply works.

Just to add to that, a lot of us concern ourselves not only with solving the problems given but also with the company people work for by trying to find ways to avoid natural server killers like While Loops and forms of RBAR. The folks o this thread wanted more detail to try to help you avoid the While Loop you had in your original post and couldn't understand that 1) you might be experimenting with no particular task in mind or 2) that you had made up your mind to use RBAR even if there might be a better way.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

pjn SSC Enthusiast Points: 191 More actions · Answer 13

I apologize if you thought I was being sarcastic. I was just after a simple yes/no answer. I am using a different method now which is working fine. The original question was more of a thought process and not an actual issue per say.

Here is more detail, that perhaps will give an idea of the structure and maybe there is a solution to my original question.

Basically we have a back end system for creating mailshots. The users can create a list of people from certain tables with certain criteria (this is a bit legacy). This is done by tick boxes extra, the system then builds an SQL query based on the selection of data and stores this SQL statement into a column in a table.

This table also contains info regarding the mailshot data, i.e is it recurring end, dates start dates. The stored statements can be very different from each other, but do contain a common key in userid.

Another table holds the scheduled information for mailshots that will go out for the rest of the year and these are processed every day to send out the emails, this table also has the userid as a key.

Now, because of a new requirement, we need to display the scheduled list, but with certain data that is returned from the stored SQL statement.

Currently the system runs a simple query against the schedule table, no joining is needed. Before me, some has done a RBAR approach, but is not efficient and could in the future bring down the web server this application uses.

So hence the initial question. It was a thought process to see if this idea on mine was possible.

I much prefer the SQL server to take the load of running queries rather than the application server, so I was trying to think of a way to join the table stored statment using EXECUTE.

The biggest issue (like most) is time and legacy designs which restrict what I can do.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 14

In that case, something like your original query could work if the EXEC were encapsulated in OPENROWSET or maybe even the less priv hungry OPENDATASOURCE. The OPENDATASOURCE method might require a "loop back" linked server that refers to itself.

We'd need more detail about the parts involved, especially what the stored queries look like, but at a 50,000 foot level, it looks to me like it could, indeed, be done and possibly without a huge effort.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)