How to include null as search parameter in sql

Question

TABLES:

tblEmployee - EmployeeId, EmployeeName    
tblActivity - EmployeeId, AppraisalId

Column AppraisalId is of datatype int and has either values 0-2 or NULL.

The Selected values of the 2 dropdowns provide the parameters for the stored procedure.

A user can search for records of all employees or just any one. Similarly, for all, none or any one type of appraisal.

What should I do if I have to search only for records that have AppraisalId as NULL. The query works fine otherwise.

ASPX:

<asp:dropdownlist id="ddlemployee" runat="server" appenddatabounditems="true">
 <asp:listitem text="--All--" value="0"/>
</asp:dropdownlist>

<asp:dropdownlist id="ddlappraisal" runat="server" appenddatabounditems="true">
 <asp:listitem text="--All--" value="-1"/> 
  <asp:listitem text="None"/>   //not sure what value this should have
  <asp:listitem text="Excellent" value="2"/>
  <asp:listitem text="Average" value="1"/>
  <asp:listitem text="Poor" value="0"/>
</asp:dropdownlist>

SQL:

alter procedure getdata
@EmployeeId int
@AppraisalId int
as
begin
 set nocount on;
 if  @EmployeeId = 0
 set @EmployeeId = null


 select e.employeename,a.appraisalid
 from tblemployee e
 left join tblactivity a on 
 ( e.employeeid = a.employeeid and 
   (e.employeeid = @Employeeid or @Employeeid is null) and
   (a.appraisalid = @AppraisalId or @AppraisalId = -1)       
 )
end

Szymon · Accepted Answer · 2014-03-09 07:25:28Z

1

You could provide a value of -2 for None option:

<asp:listitem text="None" value="-2"/>

And modify the query (showing only select here, the rest stays the same):

select e.employeename,a.appraisalid
 from tblemployee e
 left join tblactivity a on 
 e.employeeid = a.employeeid and 
   (e.employeeid = @Employeeid or @Employeeid is null) and
   (a.appraisalid = @AppraisalId or @AppraisalId = -1
       or (@AppraisalId = -2 and a.appraisalid is null)

answered Mar 9, 2014 at 7:25

Szymon

43k16 gold badges99 silver badges115 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to include null as search parameter in sql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related