SQL Statement Is Ignoring Where Parameter

SQL statement is ignoring where parameter

Fix your parentheses

SELECT * FROM people 
WHERE
    university='2' 
    AND (MATCH (lname,fname) AGAINST ('+massive' IN BOOLEAN MODE) 
         OR fname LIKE '%box%' 
         OR lname LIKE '%box%') 

AND has higher precedence than OR, so university = '2' was only being combined with MATCH, not with the fname/lname tests.

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)

Oracle PL/SQL seems to ignore WHERE clause of my Function

You have two issues:

CREATE OR REPLACE FUNCTION fn_count_members(
  p_id_status in club.members.id_status%TYPE  -- change the parameter name to
                                              -- something different from the
                                              -- column name.
)
RETURN NUMBER IS
total club.members.id%TYPE:=0;
BEGIN
    SELECT COUNT(id) INTO total 
    FROM club.members m
    WHERE m.id_status = p_id_status;          -- and here
    RETURN total; 
END;
/

Then you need to pass the parameter in rather than using 1:

select id_status,
       fn_count_members(id_status) as total
from   club.members;

db<>fiddle here

How to Ignore Null Parameters in SQL Query

Like this

...
and (@value3 is null or col3 = @value3)
and (@value4 is null or col4 = @value4)
....

Parameters.Add ignoring position of parameter in string

Try building up the field list and parameter list together in the loop.. something like this off the top of my head:

Dim commandString As String = "INSERT INTO users ({fields}) VALUES ({values})"
Dim commandFields As List(Of String) = New List(Of String)
Dim commandValues As List(Of String) = New List(Of String)

For Each control As Control In Controls
    If control.Name.StartsWith("field") Then
        commandFields.Add(control.Tag)
        commandValues.Add($"@{control.Tag}")

        command.Parameters.AddWithValue($"@{control.Tag}", control.Text)
    End If
Next

commandString = commandString.Replace("{fields}", String.Join(", ", commandFields)
commandString = commandString.Replace("{values}", String.Join(", ", commandValues)

Ignoring null parameter in where clause linq to sql

You can add those conditions to the query. It won't make for the most readable SQL, but assuming you find readable code more important and trust SQL Server's optimizer:

members = db.Members.Where(x =>
    (package == null || x.PackageName == package) &&
    (packageStatus == null || x.PackageStatus == packageStatus) &&
    (branch == null || x.Branch == branch)
).ToList();
        

Alternatively, you could conditionally append Where()s to a variable of type IQueryable<Member>. See for example entity framework: conditional filter.

ignore where statement if parameter is null in doctrine

You should use the QueryBuilder for this, to do it more efficiently, I'll show you how you do yours and then the same with the QueryBuilder as example:

Yours:

public function getAllSearchedTickets($play,$teater){
    $query = 'select s from mtadminBundle:ReserveLocation s'.
        'join s.reserve a'.
        'join a.sance b'.
        'where a.acceptCode != 0');

    $paramArray = array();
    if( $play ) {
        $query .= ' and b.play = :play';
        $paramArray['play'] = $play;
    }
    if( $teater ) {
        $query .= ' and b.teater = :teater';
        $paramArray['teater '] = $teater;
    }

    return $this->getEntityManager()->createQuery($query)
        ->setParameters($paramArray)->getResult();
}

QueryBuilder:

public function getAllSearchedTickets($play,$teater){
    $queryBuilder = $this->getEntityManager()->createQueryBuilder();
    $queryBuilder->select('s')
        ->from('mtadminBundle:ReserveLocation', 's')
        ->join('s.reserve', 'a')
        ->join('a.sance', 'b')
        ->where('a.acceptCode != 0');

    if( $play ) {
        $queryBuilder->andWhere('b.play = :play');
        $queryBuilder->setParameter('play', $play);
    }
    if( $teater ) {
        $queryBuilder->andWhere('b.teater = :teater');
        $queryBuilder->setParameter('teater', $teater);
    }

    return $queryBuilder->getResult();
}

---

Related Topics