SQL Server : check if variable is Empty or NULL for WHERE clause

Question

When searching for a list of products, the @SearchType parameter is optional. If @SearchType is empty or NULL then it should return all products and not use the WHERE clause. Otherwise, if it passed Equipment it would then use that instead.

ALTER PROCEDURE [dbo].[psProducts] 
    (@SearchType varchar(50))
AS
BEGIN
    SET NOCOUNT ON;

    SELECT 
        P.[ProductId],
        P.[ProductName],
        P.[ProductPrice],
        P.[Type]
    FROM [Product] P
    -- if @Searchtype is not null then use the where clause
    WHERE p.[Type] = @SearchType
END

Solution 1

Just use

If @searchType is null means 'return the whole table' then use

WHERE p.[Type] = @SearchType OR @SearchType is NULL

If @searchType is an empty string means 'return the whole table' then use

WHERE p.[Type] = @SearchType OR @SearchType = ''

If @searchType is null or an empty string means 'return the whole table' then use

WHERE p.[Type] = @SearchType OR Coalesce(@SearchType,'') = ''

Solution 2

If you don't want to pass the parameter when you don't want to search, then you should make the parameter optional instead of assuming that '' and NULL are the same thing.

ALTER PROCEDURE [dbo].[psProducts] 
(
  @SearchType varchar(50) = NULL
)
AS
BEGIN
  SET NOCOUNT ON;

  SELECT P.[ProductId]
  ,P.[ProductName]
  ,P.[ProductPrice]
  ,P.[Type]
  FROM dbo.[Product] AS P
  WHERE p.[Type] = COALESCE(NULLIF(@SearchType, ''), p.[Type])
  OPTION (RECOMPILE);
END
GO

Now if you pass NULL, an empty string (''), or leave out the parameter, the where clause will essentially be ignored.

I added OPTION (RECOMPILE) to demonstrate that seeks are possible with the right parameter values and provided the index covers the query adequately, however a seek is very unlikely if the compilation occurred for NULL (whole table) or a parameter value that returned too many rows to make a seek worthwhile. Give it a try.

In reality, though, optional parameters that tend to lead to scans when they shouldn't should almost certainly be handled with with a combination of dynamic SQL and, for parameters where data skew can be an issue, OPTION (RECOMPILE). See my "kitchen sink" approach here, and give it a try:

• An Updated Kitchen Sink Example

Solution 3

WHERE p.[Type] = isnull(@SearchType, p.[Type])

Solution 4

Old post but worth a look for someone who stumbles upon like me

ISNULL(NULLIF(ColumnName, ' '), NULL) IS NOT NULL

ISNULL(NULLIF(ColumnName, ' '), NULL) IS NULL

View more solutions

Comments

[User970008]

It is not working. It returns no results when I pass no parameter. The products page looks for a query string for type, if not, then sets the method parameter Type="". So, I believe the Stored proc might be looking for a SearchType ="" which returns no results. I want to return ALL results.

[User970008]

Not working. See above. It returns no results, I need to return all results instead.

[Adir D]

It's not working because you're passing an empty string. An empty string is not NULL!

[Adir D]

It's not working because you're passing an empty string. An empty string is not NULL!

[markblandford]

@User970008 if that's the case then your question is wrong. There is a whole world of difference between NULL and an empty string.

[datalifenyc]

@User970008 If you have multiple WHERE conditions, you'll want to you use paranthesis: AND (p.[Type] = @SearchType OR Coalesce(@SearchType,'') = '')

[usefulBee]

@AaronBertrand, it works. Where is the empty string?

[Adir D]

@usefulBee Above, where the OP said The products page looks for a query string for type, if not, then sets the method parameter Type="". <--- that's an empty string, not NULL.

[usefulBee]

@AaronBertrand, I see...that is what they created the @ for :)

[Adir D]

@usefulBee thanks. Note my comment was almost 6 years ago, when there was no other activity on this page, and I think you could place more blame on the OP for splintering their comments across two posts.

[usefulBee]

@AaronBertrand, right, I am glad you are still around :) Happy new year to you!

[Grimm]

Great answer! However I could not get this to use an Index Seek on p.[Type] (if an index was defined on it). It always does an Index Scan (compared to an Index Seek if querying p.[Type] = 'value' directly). Do you have any idea how to optimize the query further?

[Adir D]

@Grimm It's probably for other reasons - usually a scan isn't happening simply because of syntax (e.g. how many rows match the type you're searching for, how many rows matched when you first ran the query, when you first ran your query did you specify a param or not, how many columns are in your index, how many columns are you asking for in your query?).

[Grimm]

Unfortunately not - give it a try! There is a whole other threat complaining about this (I just searched stackoverflow to investigate this further). See stackoverflow.com/questions/6186732/index-seek-with-coalesce

[Grimm]

We all now that "... WHERE col IS NULL OR col = '' " runs significantly faster than " ... WHERE ISNULL(col, '') <> '' " I guess this is for the same reason - SQL Server evaluates the expression for every row when functions like ISNULL or COALESCE are involved.

[Grimm]

Just found out that even an index hint won't avoid an Index Scan.

[Adir D]

We all now that "... WHERE col IS NULL OR col = '' " runs significantly faster than " ... WHERE ISNULL(col, '') <> '' " - what evidence do you have of that?

[Grimm]

Evidence: Own testing - again, give it a try (plus personal development experience over the years).

[Grimm]

Yes, dynamic SQL seems to be the only way to force index seeks. But of course we all try to find ways to avoid that ...

[Adir D]

@Grimm Updated my answer. I'd be careful to make gross generalizations about "this syntax is always faster than this other equivalent syntax" and "this pattern never yields a seek" because "always" and "never" are too easy to disprove. Also dynamic SQL is a perfectly fine solution to a lot of problems - don't avoid it just because it's dynamic SQL.

[Grimm]

Mea culpa - you're right! I absolutely assumed that the recompile would not help, as I am testing and rewriting direct SQL SELECTs. The interesting thing is that SQL Server "falls back" to an index scan when you comment out the (Recompile) option again (without adjusting the parameter, just rerun).

[Grimm]

Also interesting: a FORCESEEK hint gives an error message for the same query without option recompile but with the same parameter (for which an index scan would be executed).