SQL Add Filter Only If a Variable Is Not Null

Add filter only if the variable is given with values in Metabase

Would optional clauses help you here? See the end of Metabase Documentation: Sql Parameters.

Your code would essentially become something like

select *
from dashboard_demographic_view
[[where clinic_id = {{clinic}})]]

(This question has probably been asked earlier too – maybe worth checking if there are duplicates inside Stack Overflow.)

case when filter is empty - select all

You may rephrase your logic as follows:

WHERE
    ... AND
    (state = @stateFilter OR @stateFilter = '');

The last condition would return true if the state equals the variable passed in, or the variable passed in happens to be empty string.

How to filter records on condition if filter value is NULL in SQL Server?

this is simple OR

...
AND (@carEngineNo is NULL or CarLog.engine_no = @carEngineNo)

think of appending option(recompile) to your query since actual parameter set may differ.

---

your case fixed:

AND CarLog.engine_no = 
        CASE
        When @carEngineNo IS NOT NULL THEN @carEngineNo 
        WHEN @carEngineNo IS NULL THEN CarLog.engine_no
        END

an alternate:

AND CarLog.engine_no = IsNull(@carEngineNo, CarLog.engine_no)

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

How to apply a Hasura `where` filter only if a variable is not null?

This behaviour changed somewhere between v1.3.4. Therefore there are two correct answers.

You can read more about this change in the hasura repository.

Before Version 1.3.4

This answer describes it.

After Version 1.3.4

Using null in comparisons is dangerous when defaulting to true because an accidentally unset variable could result in a dropped table. The maintainers removed this behaviour but made it accessible by setting the variable HASURA_GRAPHQL_V1_BOOLEAN_NULL_COLLAPSE.

When comparing with {_eq: null}, Hasura will throw an error because it is assumed that this is a mistake.

If you want to compare to a value and evaluate to true when the value is null, you need to handle the case on the client side and pass the whole boolean expression to Hasura.

query getUsers ($userSetsWhere: user_sets_bool_exp) {
  user(where: { user_sets: { $userSetsWhere } }) {
    id
    name
    status
    user_sets {
      set {
        name
      }
    }
    # more fields...
  }
}

const userSetsWhere = setId ? { set_id: { _eq: $setId } } : {};

What it does, is that only in case the value is not null or undefined a non-empty expression gets passed to Hasura.

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)

filter based on variable, if no value in variable apply NO filters

Just do:

WHERE
   --only apply this filter if p_filter = 'A'
   p_filter = 'A' 
   AND
   id NOT IN (select id from t2 WHERE t1.id = t2.id(+) ) 

   OR

   --only apply this filter if p_filter = 'B'
   p_filter = 'B' 
   AND
   id NOT IN (select id from t3 WHERE t1.id = t3.id(+) )  

   OR

   -- If the variable though is null, I would like for it to apply both filters
   p_filter IS NULL
   AND
   id NOT IN (select id from t2 WHERE t1.id = t2.id(+) ) 
   AND
   id NOT IN (select id from t3 WHERE t1.id = t3.id(+) ) 

---- EDIT ---------

I am so sorry, I am stupid, I meant to say if null apply NO filters! –
user2924127

In that case just remove the last condition, and add OR p_filter IS NULL:

WHERE
   --only apply this filter if p_filter = 'A'
   p_filter = 'A' 
   AND
   id NOT IN (select id from t2 WHERE t1.id = t2.id(+) ) 

   OR

   --only apply this filter if p_filter = 'B'
   p_filter = 'B' 
   AND
   id NOT IN (select id from t3 WHERE t1.id = t3.id(+) )  

   -- I am so sorry, I am stupid, I meant to say if null apply NO filters!
   OR
   p_filter IS NULL

filtering only if it is not null or undefined

You can check if a value is null or undefined by comparing it to null (see this answer), so just OR that check into each of your filter expressions and they will only filter if the value is not null or undefined.

results.data
       .filter((x) => countries == null || countries.includes(x.country))
       .filter((x) => startYear == null || endYear == null || x.car_model_year > startYear && x.car_model_year < endYear)
       .filter((x) => gender == null || x.gender.toLowerCase() === gender.toLowerCase())
       .filter((x) => colours == null || colours.includes(x.car_color))

SQL Server Query filter query - NULL parameter

You need PhaseID instead of @PhaseID :

Select * 
from TestTable
Where (PhaseID = @PhaseID OR PhaseID IS NULL);

However, i would over thinking with :

. . .
WHERE (@PhaseID IS NOT NULL AND PhaseID = @PhaseID) OR
      (@PhaseID IS NULL AND PhaseID IS NULL);

---

Related Topics