Bring all records if parameter is different

Asked

Viewed 1,032 times

3

I have a table contrato with the countryside concluido of the kind tinyint(1), if it is 0 it is not completed and if it is 1 it is completed. I will pass this value per parameter to the query, as I do to bring all the data when the value of this parameter is different from 0 and 1?

sql query
  • 1

    Which database you are using?

2 answers

3


Use the clause OR for conditional search.

SELECT c.*
  FROM contrato c
 WHERE :parametro NOT IN (0, 1)
    OR c.concluido = :parametro;

If the parameter can be null, use one more clause OR:

SELECT c.*
  FROM contrato c
 WHERE :parametro IS NULL
    OR :parametro NOT IN (0, 1)
    OR c.concluido = :parametro;
  • 1

    Would not be NOT IN (0,1)?

  • @Willian true, I will correct

  • Yes, (0,1), gave it right. Thank you.

  • And how do I bring everything when the parameter is null? Example: /contratos

  • 1

    @Fernandozabin added in reply

1

Use the "coalesce" (default in all sql databases) to save a "or" in case your parameter comes null as well.

SELECT * FROM Contrato WHERE completed = parameter or coalesce(:parameter,-1) not in (0, 1);

Another way would be this:

SELECT * FROM Contrato WHERE completed = case when coalesce(:parametro,-1) between 0 and 1 then :parametro Else completed end

Browser other questions tagged sql query

You are not signed in. Login or sign up in order to post.