CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Control Freak
Control Freak

Reputation: 13243

Using IS NULL in a WHERE Statement

 [dbo].[StoreProc1]
 (
 @Name varchar(10) = NULL,
 @Age int
 )

 SELECT * FROM TABLE WHERE Name = @Name and Age = @Age

However, since @Name = NULL is incorrect, The stored procedure doesn't know if the input for Name was Null or Not to do Name IS NULL or Name = '<value>'

What's the quick work-around this?

I suppose i can do something like this, but isn't there an easier way?

IF @Name IS NULL 
BEGIN 
  SELECT * FROM TABLE WHERE NAME IS NULL AND AGE=@AGE
END
ELSE
BEGIN
  SELECT * FROM TABLE WHERE NAME=@NAME AND AGE=@AGE
END

Upvotes: 0

Views: 144

Answers (4)

Ric Parodi
Ric Parodi

Reputation: 1

SELECT * FROM TABLE WHERE ISNULL(NAME,'')=ISNULL(@NAME,'') AND AGE=@AGE

Upvotes: 0

Steve Stedman
Steve Stedman

Reputation: 2672

Assuming that you want to get the results when name = @Name or when they are both null, then I would go with the following.

[dbo].[StoreProc1]
(
 @Name varchar(10) = NULL,
 @Age int
)

 SELECT * 
   FROM TABLE 
  WHERE (Name = @Name 
         OR ( Name IS NULL AND @Name IS NULL) )
    AND Age = @Age

Hope this helps.

Upvotes: 1

Cade Roux
Cade Roux

Reputation: 89731

[dbo].[StoreProc1]
 (
 @Name varchar(10) = NULL,
 @Age int
 )

 SELECT * FROM TABLE WHERE (Name = @Name OR COALESCE(Name, @Name) IS NULL) and Age = @Age

Upvotes: 4

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115630

Change Name = @Name into:

( Name = @Name OR Name IS NULL AND @Name IS NULL )

Upvotes: 1

Related Questions