CODEWITHSUNDEEP
sqlsql-serversql-server-2008
pepela
pepela

Reputation: 423

Select rows where one cell value is equal to parameter or everything in ms sql

This is my select statement: 

SELECT * FROM Persons p
WHERE p.Name= ISNULL(@Name, p.Name)

it @Name is null it only selects the rows where Name is not NULL but not the one with NULL value.

What has to be done to select desired rows?

Upvotes: 1

Views: 3941

Answers (6)

Serpiton
Serpiton

Reputation: 3684

When @Name is NULL the query became 

SELECT *
FROM Persons P 
WHERE p.Name = p.Name

and NULL is not equal NULL, as it mean unknown value and two unknown values are not equal.

A way to get all the data is

SELECT *
FROM   Persons P
WHERE  COALESCE(p.Name, N'a') = COALESCE(@Name, p.Name, N'a')

so that when p.Name is NULL there is a default value to use.

Edit as the string 'NULL' as the last value of COALESCE can be confusing I changed it to another constant literal.

Upvotes: 1

DrCopyPaste
DrCopyPaste

Reputation: 4117

Seems like you want to show all entries, if @name is null and if it is not, show only the values that share the same value as @name, this should do it:

SELECT * FROM Persons WHERE @name IS NOT NULL AND name = @name OR @name IS NULL

Upvotes: 1

Deepshikha
Deepshikha

Reputation: 10274

Write as:

SELECT * FROM Persons p
WHERE 1 = case when isnull(@Name,'') = '' then 1 
          when isnull(@Name,'') <> '' and p.Name = @Name  then 1 
          end

Upvotes: 0

t-clausen.dk
t-clausen.dk

Reputation: 44326

DECLARE @name varchar(290) ='Thomas'

SELECT * FROM 
Persons P
WHERE exists(select name intersect select coalesce(@name, name))

Upvotes: 2

Thanos Markou
Thanos Markou

Reputation: 2624

If i understand correctly you need this:

SELECT * FROM Persons p
WHERE (p.Name= ISNULL(@Name, p.Name) OR Name IS NULL)

Upvotes: 0

vhadalgi
vhadalgi

Reputation: 7189

try this !

SELECT * FROM Persons p
WHERE p.Name= coalesce(@Name, p.Name)

Upvotes: 0

Related Questions