Optional Arguments in Where Clause

Optional Arguments in WHERE Clause

Alternatively to the ISNULL / COALESCE options, you can test the parameters for being null:

SELECT NAME  
FROM TABLE  
WHERE  
    (@City IS NULL OR City = @City)
AND 
    (@Gender IS NULL OR Gender = @Gender)
AND 
    (@Age IS NULL OR Age = @Age) 

Utilising optional parameters in where clause

If you're willing to sacrifice a tiny amount of time on each execution, OPTION(RECOMPILE) will provide the performance equal to dynamic SQL but without all the perils of it.

select * from table
WHERE
    (StartDate >= @StartDate or @StartDate is null) and
    (StartDate <= @EndDate or @EndDate is null) and
    (CE.ClientID = @ClientID or @ClientID is null)
option(recompile)

use optional parameter in WHERE clause

WHERE (@frst IS NULL OR 
    (
         LastName like '%' + @lst + '%'
         AND (
             FirstName like @frst + ' %'
             OR FirstName like '% ' + @frst + ' %'
             OR FirstName like '% ' + @frst
             OR FirstName = @frst
        )
    )
 )
 AND (@passportNo IS NULL OR
        Passport = @passportNo);

Edit: Actually this should be more efficient:

WHERE ( @frst IS NULL OR FirstName like '%' + @frst + '%' )
AND   ( @lst  IS NULL OR LastName  like '%' + @lst  + '%' )
AND   ( @passportNo IS NULL OR Passport = @passportNo )

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

PL/SQL Optional parameters in where

I think you want:

WHERE (T.ID_NUM = :ID_NUM OR :ID_NUM IS NULL) AND
      (ASOL.CASE_ID = :CASE_ID OR :CASE_ID IS NULL)

I am guessing you actually want AND, not OR between the conditions.

Parameterize clause optional fields WHERE clause

I'm using in this case the famous WHERE 1=1 trick

const dataObj = {
    host: "192.168,AS101",
    code: "001,025",
    country: "Colombia",
  };

  let sql = " SELECT * FROM MYTABLE WHERE 1=1 ";
  Object.entries(dataObj).map(
    (entri) => (sql = `${sql} AND ${entri[0]} in (${entri[1]})`)
  );
  console.log(sql); <-- SELECT * FROM MYTABLE WHERE 1=1  AND host in (192.168,AS101) AND code in (001,025) AND country in (Colombia)

const dataObj = {
    host: "192.168,AS101",
  };

  let sql = " SELECT * FROM MYTABLE WHERE 1=1 ";
  Object.entries(dataObj).map(
    (entri) => (sql = `${sql} AND ${entri[0]} in (${entri[1]})`)
  );
  console.log(sql); <-- SELECT * FROM MYTABLE WHERE 1=1  AND host in (192.168,AS101)

---

Related Topics