CODEWITHSUNDEEP
c#sql-servert-sqlsql-server-2008syntax
Arseni Mourzenko
Arseni Mourzenko

Reputation: 52321

How to check if a value is null in T-SQL in parametrized query?

Let's imagine there is a table Product with columns ProductId (smallint), Title (nullable nvarchar(100)) and Price (money). A title can be null.

There is a query which must return the products matching a specific title and a specific price:

using (SqlCommand getProducts = new SqlCommand("select ProductId from Product where Title = @title and Price = @price", sqlConnection))
{
    getProducts.Parameters.AddWithValue("@title", title);
    getProducts.Parameters.AddWithValue("@price", price);
}

When executing the following code with title set to null (or probably also to an empty string), for SQL Server, the comparison will be:

[...] where Title = NULL and Price = 123

which will return an empty set, since the correct syntax is:

[...] where Title is NULL and Price = 123

I could change the query string according to the null check of the title, but it will be unmaintainable.

Is there a clean way to make the comparison work without making the query string different when Title is null?

Upvotes: 0

Views: 537

Answers (2)

Craig T
Craig T

Reputation: 1061

You can use IsNull() like this...

using (SqlCommand getProducts = new SqlCommand("select ProductId from Product where IsNull(Title, '') = IsNull(@title, '') and Price = @price", sqlConnection))

If the title is null, then an empty string will be used for the comparison rather than null.

[edit] Updated after a1ex07's comment below.

Upvotes: 1

Joe Stefanelli
Joe Stefanelli

Reputation: 135808

[...] WHERE COALESCE(Title,'') = COALESCE(@Title,'') AND Price = 123

Upvotes: 2

Related Questions