0

I am working with a stored procedure where I want to add or remove WHERE clauses according to the values of a couple parameters.

This is what I've got so far:

WHERE 
   t.iDdPais = @iIdPais
   AND t.iIdRegion = @iIdRegion
   AND t.dtFecha BETWEEN @dtStart AND @dtEnd

BUT, if @iIdPais is 0 (which means ALL), I need to remove that clause from the WHERE statement, the same goes to @iIdRegion.

abatishchev
  • 98,240
  • 88
  • 296
  • 433
Multitut
  • 2,089
  • 7
  • 39
  • 63
  • Do you care about performance of the query? – Gordon Linoff Dec 01 '14 at 17:58
  • Potential duplicate of: http://stackoverflow.com/questions/810714/where-is-null-is-not-null-or-no-where-clause-depending-on-sql-server-parameter (?) – Eduard Uta Dec 01 '14 at 17:59
  • If there's any duplicate its this one: http://stackoverflow.com/questions/10185638/optional-arguments-in-where-clause – Jamiec Dec 01 '14 at 18:01
  • @GordonLinoff as it is a query that will be executed now and then and the those tables are catalog-ish, I care more about the query. – Multitut Dec 01 '14 at 18:06

2 Answers2

4

You just need to use normal boolean logic:

WHERE 
  (@iIdPaid = 0 OR t.iDdPais = @iIdPais)
  AND (@iIdRegion=0 OR t.iIdRegion = @iIdRegion)
  AND t.dtFecha BETWEEN @dtStart AND @dtEnd
Jamiec
  • 133,658
  • 13
  • 134
  • 193
4

Put the entire SELECT inside IF clause..

IF(@ildPais =0 AND @ildRegion =0)
SELECT <THE DATASET> FROM THE TABLE
WHERE 
   t.iDdPais = @iIdPais
   AND t.iIdRegion = @iIdRegion
   AND t.dtFecha BETWEEN @dtStart AND @dtEnd

ELSE 
SELECT <THE DATASET> FROM THE TABLE
--no where clause
SouravA
  • 5,147
  • 2
  • 24
  • 49
  • 1
    It is alot better than the accepted answer which will have terrible performance. – HLGEM Dec 01 '14 at 18:43
  • @HLGEM I Dont know what RDBMS system you use, but I do that on sql server with tables tens of of millions of rows without too much prolem. These sort of queries will never be the fastest, but with the right indexing that sort of query will be just fine. – Jamiec Dec 02 '14 at 08:53