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

Phil · Accepted Answer · 2012-04-23T16:18:51.580

105

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,'') = ''

edited Apr 23 '12 at 16:18

answered Apr 23 '12 at 16:10

Phil

42,255
9
100
100

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 Apr 23 '12 at 16:16
It's not working because you're passing an empty string. An empty string is not NULL! – Aaron Bertrand Apr 23 '12 at 16:20
1

@User970008 if that's the case then your question is wrong. There is a whole world of difference between NULL and an empty string. – markblandford Apr 23 '12 at 16:21
2

@User970008 If you have multiple WHERE conditions, you'll want to you use paranthesis: `AND (p.[Type] = @SearchType OR Coalesce(@SearchType,'') = '')` – datalifenyc Jan 15 '17 at 21:08

Aaron Bertrand · Answer 2 · 2021-12-14T14:47:33.847

17

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

edited Dec 14 '21 at 14:47

answered Apr 23 '12 at 16:19

Aaron Bertrand

272,866
37
466
490

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? – Grimm Dec 14 '21 at 12:15
@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?). – Aaron Bertrand Dec 14 '21 at 13:44
Unfortunately not - give it a try! There is a whole other threat complaining about this (I just searched stackoverflow to investigate this further). See https://stackoverflow.com/questions/6186732/index-seek-with-coalesce – Grimm Dec 14 '21 at 13:58
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 Dec 14 '21 at 14:02
Just found out that even an index hint won't avoid an Index Scan. – Grimm Dec 14 '21 at 14:11
`We all now that "... WHERE col IS NULL OR col = '' " runs significantly faster than " ... WHERE ISNULL(col, '') <> '' "` - what evidence do you have of that? – Aaron Bertrand Dec 14 '21 at 14:27
Evidence: Own testing - again, give it a try (plus personal development experience over the years). – Grimm Dec 14 '21 at 14:43
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 ... – Grimm Dec 14 '21 at 14:46
1

@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. – Aaron Bertrand Dec 14 '21 at 14:48
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 Dec 14 '21 at 14:59
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). – Grimm Dec 14 '21 at 15:03

score 6 · Answer 3 · answered Apr 23 '12 at 16:12

6

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

answered Apr 23 '12 at 16:12

D'Arcy Rittich

167,292
40
290
283

1

Not working. See above. It returns no results, I need to return all results instead. – User970008 Apr 23 '12 at 16:17
4

It's not working because you're passing an empty string. An empty string is not NULL! – Aaron Bertrand Apr 23 '12 at 16:20

score 2 · Answer 4 · answered Sep 22 '16 at 11:55

2

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

answered Sep 22 '16 at 11:55

singhswat

832
7
20

Ali Umair · Answer 5 · 2015-10-19T07:26:58.463

If you can use some dynamic query, you can use LEN . It will give false on both empty and null string. By this way you can implement the option parameter.

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

DECLARE @Query nvarchar(max) = N'
    SELECT 
        P.[ProductId],
        P.[ProductName],
        P.[ProductPrice],
        P.[Type]
    FROM [Product] P'
    -- if @Searchtype is not null then use the where clause
    SET @Query = CASE WHEN LEN(@SearchType) > 0 THEN @Query + ' WHERE p.[Type] = ' + ''''+ @SearchType + '''' ELSE @Query END   

    EXECUTE sp_executesql @Query
    PRINT @Query
END

score 0 · Answer 6 · answered Sep 01 '22 at 14:05

0

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

Ans:

In above Case: you can use

Where (p.Type is null and @SearchType is null) or (p.Type is not null and p.Type = @SearchType)

answered Sep 01 '22 at 14:05

TDekate

1
1

1

Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – jasie Sep 06 '22 at 12:32

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

6 Answers6

Linked

Related