CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sqldynamic
wootscootinboogie
wootscootinboogie

Reputation: 8695

Dynamic SQL with variable number of table joins

My table icdClm is a junction table between a table with person demographics and another table that lists various codes of interest related to that person. I'd like to be able to join these tables a variable number of times. I'm not interested in the other table involved in the relationship because of simplicity's sake at this point. I want a while loop to help generate the query (if there is a better way I'm open to a different implementation.)

Below is the desired output if the query were to run twice (two times for the two derived tables).

select distinct x1.patid
from
(
select ic.patid
    from icdClm as ic
    where ic.icd like '123%'
) x1 inner join 
(
select ic.patid
    from icdClm as ic
    where ic.icd like '456%'
)x2 on x1.patid=x2.patid
inner join 
(
select ic.patid
    from icdClm as ic
    where ic.icd like '789%'

) x3 on x1.patid=x3.patid

notice that they alias for the derived tables increases by 1 each time. Now for my code. This code does not generate an error, but I left it running for over 10 minutes and no results had yet been returned. Eventually, I would like to make another stored procedure dependent on this one that asks you the number of derived tables you want and then fills in the where ic.icd like '123%' with parameters inputted from the user, but first things first: what is not working with the below query?

declare 
@x int
,@y int
,@sql nvarchar(1000)

select 
@x=1
,@y=3
,@sql =
'select distinct x1.patid
from
(
select ic.patid
    from icdClm as ic
    where ic.icd like ''123%''
) x1'
while @x < @y
begin;
set @sql=@sql+ 
'inner join 
(
    select ic.pati
    from icdClm as ic
    where ic.icd like ''456%''
) x1 on x'+CAST(@y as CHAR(10))+'.patid=x1.patid'
set @y=@y+1
end;
print @sql

Upvotes: 0

Views: 2688

Answers (2)

paparazzo
paparazzo

Reputation: 45096

It looks like you are tying to do an and condition of multiple terms.
If you use Intersect then you don't need unique names.
I do this but I build it up in C#.
Sorry for lack of SP example.

select patid
    from icdClm 
    where icd like '123%'
Intersect
select patid
    from icdClm 
    where icd like '456%'

EXCEPT and INTERSECT (Transact-SQL)

I found Intersect to be efficient.
With more than 5 joins I had a problem with query optimizer getting what I called defensive and producing poor response time WITH MY DATA.
Intersect does it one at a time in a brute force manner and that may or may not be good for your data.
Since your like does not have a wild card at the beginning it may use an index.

Upvotes: 2

Conrad Frix
Conrad Frix

Reputation: 52645

while @x < @y
begin;

set @y=@y+1
end;

@y is what is incremented so this loop will either never execute (because y is less than x) or it will be an infinite loop* because @y will always be larger than @x

perhaps you meant @x=@x+1

*You'll probably get an in integer overflow eventually

Upvotes: 2

Related Questions