Question 349940 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Return all rows if -1 is passed, otherwise rows that match the value

I have the below SQL query, where I want to return all rows of the table if -1 is passed as the value for @statusParam. And rows that matches the @statusParam value if something passed other than -1.

The status column is a int type column.

set @statusParam = -1;
SELECT * FROM `db`.table_name where `status` = -1 or `status` = @statusParam;

The above query is perfectly working fine for values 1 and 0. Where when 1 is passed from @statusParam, the rows that contains status value of 1 are returned. And same for the value 0.

But when I pass -1 value as the value for the @statusParam the SQL query returns empty. Where expected results were to have all rows (that contains 1 and 0 for status column)

Tags:

2

Answers


  1. 0

    I don’t know why you query isn’t working, but this will:

    SELECT *
FROM db.table_name
WHERE @statusParam in (status, -1)
    Login or Signup to reply.
  2. 0

    Add the similar line in your WHERE clause

    IF(@statusParam = -1,true,`status` = @statusParam)

    It will return the all values if the @statusParam equals to -1, if it’s not it will use status = @statusParam condition.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search