CODEWITHSUNDEEP
sqlsql-serverif-statementwhere-clause
User970008
User970008

Reputation: 1155

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

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

Upvotes: 41

Views: 186191

Answers (6)

TDekate
TDekate

Reputation: 1

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)

Upvotes: 0

Aaron Bertrand
Aaron Bertrand

Reputation: 280431

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.

enter image description here

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:

Upvotes: 17

singhswat
singhswat

Reputation: 906

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

Upvotes: 2

Ali Umair
Ali Umair

Reputation: 1424

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

Upvotes: 0

Phil
Phil

Reputation: 43001

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

Upvotes: 106

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171491

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

Upvotes: 6

Related Questions