My query filtering is not working the way I want it to

Question

I have 3 ways I want to filter:

1. by name
2. by list
3. and show all

I'm using ASP.NET 3.5 and SQL Server 2008. Using ADO.NET and stored procs.

I'm passing my list as a table valued parameter (but I'm testing with a table variable) and the name as a nvarchar. I have "show all" as ISNULL(@var, column) = column. Obviously the way I'm querying this is not taking advantage of short circuiting or my understanding of how WHERE clauses work is lacking. What's happening is if I make @var = 'some string' and insert a null to the table variable, then it filters correctly. If I make @var = null and insert 'some string' to the table variable, then I get every record, where I should be getting 'some string'.

The code:

declare @resp1 nvarchar(32)
set @resp1 = null
declare @usersTable table
(responsible nvarchar(32))
--insert into @usersTable (responsible) values (null)
insert into @usersTable (responsible) values ('ssimpson')
insert into @usersTable (responsible) values ('kwilcox')
select uT.responsible, jsq.jobnumber, jsq.qid, aq.question, aq.section, aq.seq, answers.* 
from answers
inner join jobno_specific_questions as jsq on answers.jqid = jsq.jqid
inner join apqp_questions as aq on jsq.qid = aq.qid
left join @usersTable as uT on uT.responsible = answers.responsible
where answers.taskAction = 1 and (uT.responsible is not null or ISNULL(@resp1, Answers.responsible) = Answers.responsible)
order by aq.section, jsq.jobnumber, answers.priority, aq.seq

This is what I've come up with. It's ugly though....

declare @resp1 nvarchar(32)
set @resp1 = 'rrox'
declare @filterPick int
declare @usersTable table
(responsible nvarchar(32))
insert into @usersTable (responsible) values (null)
--insert into @usersTable (responsible) values ('ssimpson')
--insert into @usersTable (responsible) values ('kwilcox')
if @resp1 is null 
begin
   set @filterPick = 2
end
else
begin
   set @filterPick = 1
end
select uT.responsible, jsq.jobnumber, jsq.qid, aq.question, aq.section, aq.seq, answers.* 
from answers
inner join jobno_specific_questions as jsq on answers.jqid = jsq.jqid
inner join apqp_questions as aq on jsq.qid = aq.qid
left join @usersTable as uT on uT.responsible = answers.responsible
where answers.taskAction = 1 and 
(case
    when uT.responsible is not null then 2
    when ISNULL(@resp1, Answers.responsible) = Answers.responsible then 1          
 end = @filterPick )
order by aq.section, jsq.jobnumber, answers.priority, aq.seq

Ok. I think I've got it. I've removed @resp1 because it wasn't necessary and am just using the table valued parameter @usersTable (but here I'm using a table variable for testing). I've added a flag @filterPick so I can show only values in @usersTable or every record where answers.taskAction = 1.

The code:

declare @filterPick bit 
declare @usersTable table
(responsible nvarchar(32))
insert into @usersTable (responsible) values (null)
--insert into @usersTable (responsible) values ('ssimpson')
--insert into @usersTable (responsible) values ('kwilcox')
if exists (select * from @usersTable where responsible is not null)
   begin
      set @filterPick = 1
   end
else
   begin
      set @filterPick = 0
   end
select *
from answers
inner join jobno_specific_questions as jsq on answers.jqid = jsq.jqid
inner join apqp_questions as aq on jsq.qid = aq.qid
left join @usersTable as uT on answers.responsible = uT.responsible
where answers.taskAction = 1 and (uT.responsible is not null or (isnull(uT.responsible, answers.responsible) = answers.responsible and @filterPick = 0))
order by aq.section, jsq.jobnumber, answers.priority, aq.seq

Answer 1

Have you considered changing the WHERE clause to something like:

WHERE Answers.taskAction = 1 
AND(ISNULL(@resp1, Answers.responsible) = Answers.responsible 
    OR Answers.responsible IN (SELECT responsible FROM uT))

Instead of JOINing on the table-valued parameter?

Answer 2

I'm a bit confused by your question but I'll give it a shot.

First off i suspect your issues with the incorrect records being returned have to do with your comparison of a null value. To demonstrate what I am talking about query any table you want and add this to the end:

WHERE null = null

no records will be returned. In your code I would change:

where answers.taskAction = 1 and (uT.responsible is not null or ISNULL(@resp1, Answers.responsible) = Answers.responsible)

to

where answers.taskAction = 1 and (uT.responsible is not null or @resp1 is null)

And see if that returns the desired result.

Answer 3

It's in the original post. Third code block.