[sql] 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

This question is related to sql sql-server if-statement where-clause

The answer is


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

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

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

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]);
END
GO

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


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

Examples related to sql

Passing multiple values for same variable in stored procedure SQL permissions for roles Generic XSLT Search and Replace template Access And/Or exclusions Pyspark: Filter dataframe based on multiple conditions Subtracting 1 day from a timestamp date PYODBC--Data source name not found and no default driver specified select rows in sql with latest date for each ID repeated multiple times ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database

Examples related to sql-server

Passing multiple values for same variable in stored procedure SQL permissions for roles Count the Number of Tables in a SQL Server Database Visual Studio 2017 does not have Business Intelligence Integration Services/Projects ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database How to create temp table using Create statement in SQL Server? SQL Query Where Date = Today Minus 7 Days How do I pass a list as a parameter in a stored procedure? SQL Server date format yyyymmdd

Examples related to if-statement

How to use *ngIf else? SQL Server IF EXISTS THEN 1 ELSE 2 What is a good practice to check if an environmental variable exists or not? Using OR operator in a jquery if statement R multiple conditions in if statement Syntax for an If statement using a boolean How to have multiple conditions for one if statement in python Ifelse statement in R with multiple conditions If strings starts with in PowerShell Multiple conditions in an IF statement in Excel VBA

Examples related to where-clause

Laravel where on relationship object MySQL Select last 7 days SQLSTATE[42S22]: Column not found: 1054 Unknown column - Laravel Conditional WHERE clause in SQL Server Conditional WHERE clause with CASE statement in Oracle How to write a SQL DELETE statement with a SELECT statement in the WHERE clause? MySQL Multiple Where Clause How to use If Statement in Where Clause in SQL? MySQL direct INSERT INTO with WHERE clause MySql Inner Join with WHERE clause