How to use WHERE IN syntax conditionally

Hi,
I've got a dynamic table called @v_filter_table that is populated with some rows, ala:

Now, I have a query that executes, and one of the filter criteria in the query filters on the contents of this table - ala:

The problem is that I want the query to use it's own filter criteria if it can't match any rows in @v_filter_table with the location_id of 102 - since by making the filter criterion equal itself - mytable.row_id = mytable.row_id - it effectively removes itself from the WHERE clause - which is exactly what I want. E.g.

The problem is, the above doesn't work if there is more then one matching row in the @v_filter_table. E.g. If I removed one of the rows with a location_id of 102, it'll work - but otherwise it won't. The return of the COALESCE statement evidently cannot be more then one row.

How would I be able to refactor this query to acheive the result I need? Any help greatly appericated!

re: How to use WHERE IN syntax conditionally

Hello,

How the COALESCE works??
i=COALESCE(id,myid)
i=id, if id has any value
i=myid, if id is null
so, based on this you try....

I given one example below try this...


declare @t1 Table (id int, id1 int)
insert into @t1 (id,id1)
select 1,2 union all
select 2,2 union all
select 3,3 union all
select 4,4

declare @t2 Table (myid int, id int,id2 int)
insert into @t2 (myid,id,id2)
select 1,1,1 union all
select 2,2,2 union all
select 3,3,3 union all
select 4,5,6

declare @i int
SELECT * from @t2 t1
WHERE t1.id
= COALESCE(@i ,t1.id)


Ganesh

re: How to use WHERE IN syntax conditionally

So basically, whether row_id in on on the @vfilter_table.location_id or not, you want to return the record from yourtable?

If yes, that sounds like a LEFT JOIN.

-- CK