maqskywalker
asked on
sql server where clause on bit datatype fields
I'm using sql server 2008.
I have a stored procedure that returns a column that is Bit dataype called [CurrentlyEmployed]
I'm filtering like this
WHERE [CurrentlyEmployed] = @EmployeeStatus]
When I feed @EmployeeStatus = 1 to my stored procedure it returns all the employees that are currently employed
When I feed @EmployeeStatus = 0 to my stored procedure it returns all the employees that are not currently employed
How do i revise my were clause to return "All Employees".?
So this is all employees that are currently employed and also the ones not employed.
I have a stored procedure that returns a column that is Bit dataype called [CurrentlyEmployed]
I'm filtering like this
WHERE [CurrentlyEmployed] = @EmployeeStatus]
When I feed @EmployeeStatus = 1 to my stored procedure it returns all the employees that are currently employed
When I feed @EmployeeStatus = 0 to my stored procedure it returns all the employees that are not currently employed
How do i revise my were clause to return "All Employees".?
So this is all employees that are currently employed and also the ones not employed.
Ok got...
SET CurrentlyEmployed = NULL -> In case you want to return all the statement. Otherwise pass 1 or 0 as usual. Just change the where clause as mentioned below.
WHERE
( [CurrentlyEmployed] = @EmployeeStatus] OR CurrentlyEmployed IS NULL )
It will return all records if we pass NULL in the parameter CurrentlyEmployed. It is basically no filtration on CurrentlyEmployed column. :)
SET CurrentlyEmployed = NULL -> In case you want to return all the statement. Otherwise pass 1 or 0 as usual. Just change the where clause as mentioned below.
WHERE
( [CurrentlyEmployed] = @EmployeeStatus] OR CurrentlyEmployed IS NULL )
It will return all records if we pass NULL in the parameter CurrentlyEmployed. It is basically no filtration on CurrentlyEmployed column. :)
I would use
and pass 2 to get all...
WHERE (([CurrentlyEmployed] = @EmployeeStatus) or (@EmployeeStatus=2))and pass 2 to get all...
@Hainkurt - You are just using same logic/code as mine. we can pass any value but remember passing NULL will give you idea that you dont need that filter at all.
using null may require lots of changes in app or stored procedure etc...
but if we use 2 instead, we just need to change where condition and pass 2, no more changes...
but if we use 2 instead, we just need to change where condition and pass 2, no more changes...
Negative. read the question again. this is stored procedure. If we can pass 2 what the problem with NULL.
I read but it is not clear to me what we have :)
sp returns a column that is Bit dataype called [CurrentlyEmployed]
filtering like WHERE ...
does not make too much sense to me...
cannot see big picture :)
I suggested to use "0: unemployed, 1:employed, 2:all"
because I tought we have some apps that calls a stored procedure which returns list of employers...
this way, just adding "or (@EmployeeStatus=2)" to where statement and pass 2 to get all employees is sufficient...
no more code change required anywhere...
I have a stored procedure that returns a column that is Bit dataype called [CurrentlyEmployed]
I'm filtering like this
WHERE [CurrentlyEmployed] = @EmployeeStatus]
sp returns a column that is Bit dataype called [CurrentlyEmployed]
filtering like WHERE ...
does not make too much sense to me...
cannot see big picture :)
I suggested to use "0: unemployed, 1:employed, 2:all"
because I tought we have some apps that calls a stored procedure which returns list of employers...
this way, just adding "or (@EmployeeStatus=2)" to where statement and pass 2 to get all employees is sufficient...
no more code change required anywhere...
@Hainkurt - This is very common.
We want to get records for all employees, records of current employees , records of past employees.
So we can create a filter on CurrentlyEmployed , there pass 0 for past employees. , 1 for current employees and pass NULL/or any other value to get all records. so effective the sql will become
SET CurrentlyEmployed = NULL
WHERE
( [CurrentlyEmployed] = @EmployeeStatus] OR CurrentlyEmployed IS NULL )
We want to get records for all employees, records of current employees , records of past employees.
So we can create a filter on CurrentlyEmployed , there pass 0 for past employees. , 1 for current employees and pass NULL/or any other value to get all records. so effective the sql will become
SET CurrentlyEmployed = NULL
WHERE
( [CurrentlyEmployed] = @EmployeeStatus] OR CurrentlyEmployed IS NULL )
I know there is not much difference between
and
difference is, as I said before, I thought this is called from some apps, and adding nulls to left and right may require lots of code changes...
but if we just pass 2 we dont need to change anything else just where part...
and I use NULL in my sps as default values and do not pass anything to sp if I dont filter it and use the method you said...
but if we have existing codes, using 2 is better option...
WHERE (([CurrentlyEmployed] = @EmployeeStatus) or (@EmployeeStatus = 2))and
WHERE (([CurrentlyEmployed] = @EmployeeStatus) or (@EmployeeStatus is NULL))difference is, as I said before, I thought this is called from some apps, and adding nulls to left and right may require lots of code changes...
but if we just pass 2 we dont need to change anything else just where part...
and I use NULL in my sps as default values and do not pass anything to sp if I dont filter it and use the method you said...
but if we have existing codes, using 2 is better option...
@HainKurt..
We just have to modify the sp..value you are already passing. whats the point.
with any data type you can pass NULL. your 2 thing is not making any sense here please understand. It is not adding any value.
I can even pass 20 .. I am leaving this to author... I am here to provide the generic solution. I can do this also.. so there are N number of ways.
NULL will give you the information like you are not having filter on the data.
We just have to modify the sp..value you are already passing. whats the point.
with any data type you can pass NULL. your 2 thing is not making any sense here please understand. It is not adding any value.
I can even pass 20 .. I am leaving this to author... I am here to provide the generic solution. I can do this also.. so there are N number of ways.
IF @EmployeeStatus = 20 /*Get 20 from anywhere UI or app */
SET @EmployeeStatus = NULL
WHERE
( [CurrentlyEmployed] = @EmployeeStatus] OR @EmployeeStatus IS NULL )NULL will give you the information like you are not having filter on the data.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
In that case do not use any filter.