Where Is Null, Is Not Null or No Where Clause Depending on SQL Server Parameter Value

WHERE IS NULL, IS NOT NULL or NO WHERE clause depending on SQL Server parameter value

Here is how you can solve this using a single WHERE clause:

WHERE (@myParm = value1 AND MyColumn IS NULL)
OR  (@myParm = value2 AND MyColumn IS NOT NULL)
OR  (@myParm = value3)

A naïve usage of the CASE statement does not work, by this I mean the following:

SELECT Field1, Field2 FROM MyTable
WHERE CASE @myParam
    WHEN value1 THEN MyColumn IS NULL
    WHEN value2 THEN MyColumn IS NOT NULL
    WHEN value3 THEN TRUE
END

It is possible to solve this using a case statement, see onedaywhen's answer

Optional 'IS NULL' WHERE clause based on a parameter

WHERE [column1] = 'some value'
AND (@param1 IS NOT NULL OR [column2] IS NULL)

If @param1 is set, the second condition will always evaluate to true, if not, it will check if [column2] is null

NULL value in where clause

By default, in SQL Server, comparison operators return UNKNOWN (i.e. not true or false) if either value is a null.

MSDN IS [NOT] NULL documentation

There are ways to change how SQL Server handles null comparisons with things like SET ANSI_NULLS. Definitely pay attention to upcoming changes to the default for this value.

Here is an interesting article which covers several issues related to null handling.

SQL WHERE clause with parameter - NULL should match depending on parameter value

What you want is all the rows where a.Column1 = @Value and nulls only if @Value = 1:

SELECT * 
FROM tablename
WHERE (@Value = 1 AND Column1 IS NULL)
   OR (Column1 = @Value)

query using the where clause if variables are not null

Read

ALTER PROCEDURE [dbo].[spPagination] -- ORDER BY id
  @filterCol NVARCHAR(20) = NULL, --<<<<
  @filterValue NVARCHAR(40) = NULL, --<<<<
  @PageNumber INT,
  @PageSize   INT
AS
BEGIN
  SET NOCOUNT ON;

    DECLARE @SQL NVARCHAR(max)
    SET @SQL='SELECT      Emp.id , Emp.email, Emp.[firstName], Emp.[lastName], Emp.[salary], Emp.[startDateWork], Emp.age  , Rol.[name] as Role
    FROM        [dbo].tblEmployees5m    Emp
    inner join  [dbo].[tblRoles]        Rol
    ON          Emp.roleId = Rol.id
    WHERE 1=1'

    IF ISNULL(@filterCol,'')!='' AND ISNULL(@filterCol,'')!=''
        SET @SQL= @SQL+' AND @fc LIKE ''%@fv%'''

    SET @SQL = @SQL+' ORDER BY    id'

    IF ISNULL(@PageNumber,'')!='' AND ISNULL(@PageSize,'')!=''
        SET @SQL= @SQL+'
        OFFSET  @PS * (@PN - 1)  ROWS
        FETCH NEXT  @PN ROWS ONLY OPTION (RECOMPILE);'

    EXEC SP_EXECUTESQL @SQL, N'@fc NVARCHAR(20) ,@fv NVARCHAR(40) ,@PN INT,@PS INT',@fc=@filterCol,@fv=@filterValue,@PG=@PageNumber,@PS=@PageSize

    select count(1) as totalCount from [dbo].tblEmployees5m
END

SQL ignore part of WHERE if parameter is null

How about something like

SELECT Id, col1, col2, col3, col4 
FROM    myTable 
WHERE   col1 LIKE @Param1+'%'
OR      @Param1 IS NULL

in this specific case you could have also used

SELECT Id, col1, col2, col3, col4 
FROM    myTable 
WHERE   col1 LIKE ISNULL(@Param1,'')+'%'

But in general you can try something like

SELECT Id, col1, col2, col3, col4 
FROM    myTable 
WHERE   (condition1 OR @Param1 IS NULL)
AND     (condition2 OR @Param2 IS NULL)
AND     (condition3 OR @Param3 IS NULL)
...
AND     (conditionN OR @ParamN IS NULL)

Checking an input param if not Null and using it in where in SQL Server

You can use IsNull

 where some_column = IsNull(@yourvariable, 'valueifnull')

EDIT:

What you described in the comment can be done like:

where (@code is null or code = @code)

Alter WHERE clause based on parameter

I would recommend boolean logic instead:

WHERE 
    (@active = 1 AND vp.date_installed IS NOT NULL AND vp.date_removed IS NULL)
    OR (@active = 0 AND vp.date_removed IS NOT NULL)

---

Related Topics