SQL Server How to take all values from SQL table column on condition?

vngu2lb8 于 2023-03-17 发布在其他

关注(0)|答案(6)|浏览(152)

I want to select all column values irrespective of NULL as column value.

My problem is when I try to select values using IS NULL I'm getting such records also these having NULL as a value.

declare @status varchar(50)=NULL
declare @path varchar(50)= 'India'

select 
*
from [vwMYDATA] 
where
Path like '%' + @path + '%' and
(Status = @status or @status IS NULL)

vwMYDATA

path    status
INDIA1  NULL  
INDIA2  close
INDIA3  open

If I execute above query I'm getting all records whose status column has NULL value. Expected is if no status value specified show all status records:

path    status
INDIA1  NULL  
INDIA2  close
INDIA3  open

It is showing:

path    status
INDIA1  NULL

I'm passing these 2 parameters to stored procedure which is having this select statement.

How can I filter out and show all the records if status is not given as input?

sql-server

来源：https://stackoverflow.com/questions/18827988/how-to-take-all-values-from-sql-table-column-on-condition

6条答案

按热度按时间

fiei3ece1#

select 
*
from [vwMYDATA] 
where
Path like '%' + @path + '%' and
((@status IS NULL )OR (Status = @status or status IS NULL))

赞(0）回复(0）举报 2023-03-17

3okqufwl2#

You need to change the WHERE clause to a Case statement if you want different actions based on whether the @status varchar is NULL.

Select *
From [vwMYDATA]
Where
    Path Like Concat('%', @path, '%') And
    1 = (
        Case When @status Is Null Then 1
        When Status = @status Then 1
        Else 0
    )

This might be inefficient and nonperformant. For performance, it would be better to have a completely different query that matches based on NULL or not, so that proper indexing can be applied.

赞(0）回复(0）举报 2023-03-17

fcipmucu3#

If you're happy to always filter out records with a null status (which it seems you are) then this should work:

declare @status varchar(50)=NULL
declare @path varchar(50)= 'India'

select 
*
from [vwMYDATA] 
where
Path like '%' + @path + '%' and
(Status = @status or @status IS NULL)
AND Status IS NOT NULL

赞(0）回复(0）举报 2023-03-17

agxfikkp4#

Just try remove or @status IS NULL condition

select 
*
from [vwMYDATA] 
where
Path like '%' + @path + '%' and
(Status = @status)

赞(0）回复(0）举报 2023-03-17

2ekbmq325#

You need to change your condition like below:

declare @status varchar(50)=NULL
declare @path varchar(50)= 'India'

select 
*
from [vwMYDATA] 
where
Path like '%' + @path + '%' and
(@status IS NULL OR Status = @status)

you just need to change the place of condition because the conditions check from first, so now if the @status will be null, query don't check next condition.

赞(0）回复(0）举报 2023-03-17

0s0u357o6#

Get NUll Value Records on Status select * from vwMYDATA where status is null

Get Not NUll Value Records on Status select * from vwMYDATA where status is not null

赞(0）回复(0）举报 2023-03-17