"selective" join

[liuk]

Hi,
my stored accept a string parameter with the id (separated with comma) of the elements to filter . I convert this parameter into a temporary table (with only one column ID).
Now, i would that when the parameter isn't set the procedure returns all the elements from a table, when it is set (to one or more value) the procedure returns only the data that match that filter.

this is what i use now, but it's too slow.

SELECT col1,col2,...
FROM myTAB
WHERE (@id='' or exists(select ID from #tmpTab where #tempTab.ID = myTab.ID))

#temptab is filled with the value of the parameter @id.

How can i get the same result with better performance ?

thank you

 

[gmmastros]

If @id = '' Then
  Begin
    SELECT  col1,col2,...
    FROM    myTAB 
    WHERE   
  End
Else
  Begin
    SELECT  col1,col2,...
    FROM    myTAB 
            Inner Join #tmpTab
              On myTAB.ID = #tmpTab.Id
  End

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[liuk]

I forgot to say that i have (at now) three of these parameter and i don't want to write all combinations (in the future i might have other parameter to handle).
each paramater is converted into a temporary table.

I tried also to write something like this :

select col1, col2,..
from myTempTab inner join #tempTab on (@id='' or #temptab.id=myTempTab.Id)

but this work only when the parameter is set (otherwise the #temptab is empty and the join returns nothing).

Basically i want to join the main table with that created from the parameter only when the parameter is set.