My query filtering doesn't work the way I want

Question

I have 3 ways that I want to filter:

• by name
• by the list
• and show all

I am using ASP.NET 3.5 and SQL Server 2008. Using ADO.NET and stored procedures.

I pass my list as a table dependent parameter (but I'm testing a table variable) and the name as nvarchar. I have a "show everything" as ISNULL (@var, column) = column. Obviously, the way I'm asking about this does not use a short circuit or my understanding of how the WHERE clauses work. What happens if I create @var = 'some string' and put zero in a table variable, then it filters correctly. If I do @var = null and insert "some row" in a table variable, I get every entry where I need to get "some row".

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 came 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. , . @resp1, , tableusued tableususTable ( ). @filterPick, @usersTable , answer.taskAction = 1.

:

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

, .

, , . , , , :

WHERE null = null

. :

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

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

, .

Answer 2

WHERE :

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

JOINING ?