I want to filter a record....
If statusid is null, filter the record (where statusId is not null)
If statusid is not null, filter the record where statusid is equal to the specified statusid.
How do I do this?
This question is related to
sql
sql-server
database
sql-server-2008
Just like you said
select * from tbl where statusid is null
or
select * from tbl where statusid is not null
If your statusid is not null, then it will be selected just fine when you have an actual value, no need for any "if" logic if that is what you were thinking
select * from tbl where statusid = 123 -- the record(s) returned will not have null statusid
if you want to select where it is null or a value, try
select * from tbl where statusid = 123 or statusid is null
How about statusid = statusid. Null is never equal to null.
set ansi_nulls off go select * from table t inner join otherTable o on t.statusid = o.statusid go set ansi_nulls on go
I think this could work:
select * from tbl where statusid = isnull(@statusid,statusid)
Wherever you are trying to check NULL value of a column, you should use
IS NULL and IS NOT NULL
You should not use =NULL or ==NULL
Example(NULL)
select * from user_registration where registered_time IS NULL
will return the rows with registered_time value is NULL
Example(NOT NULL)
select * from user_registration where registered_time IS NOT NULL
will return the rows with registered_time value is NOT NULL
Note: The keyword null, not null and is are not case sensitive.
WHERE something IS NULL
and
WHERE something IS NOT NULL
Source: Stackoverflow.com