CODEWITHSUNDEEP
sqlnull
TheTechGuy
TheTechGuy

Reputation: 17354

How to return both null and not-null values in one query

I have an integer field person_id with the default value of NULL. I am using a stored procedure and pulling people names and address. if a user does not pass a value all records are pulled both, null and not nulls. How do I do it. May be Case statement or otherwise?

my_procedure(          <--- this is pseudo code
@person_id int null
begin
select name, address from PERSON
WHERE person_id like case NULL then NULL else '%'
end

Null requires -> IS NULL number requires = 123

EDIT I need a condition too, if nothing is passed, all values are pulled. If something is passed, that value is searched

Upvotes: 3

Views: 5893

Answers (2)

TheTechGuy
TheTechGuy

Reputation: 17354

The correct code in this case is

IsNULL(mycolum,'') LIKE CASE when person_id is null then '%' else '%' + person_id + '%'

IsNULL coverts null values into empty string and then you can worry about only strings, not null values.

Upvotes: 0

JNK
JNK

Reputation: 65157

WHERE (@personID is NULL OR personid IS NULL OR personid = @personid)

Just put both your cases in a parenthetical WHERE clause.

EDIT

Added another OR clause to cover what I THINK you are asking for.

Upvotes: 2

Related Questions