CODEWITHSUNDEEP
sqlsql-serverdatabase
Sevastsyan Oseev
Sevastsyan Oseev

Reputation: 27

If else in Where Clause SQL

I faced a problem with a SQL query. I have a table with 10 fields. I need to create a query, which gets date by field ProductionYear(int) between 2 variables @startDate(int) and @endDate(int). Both of these variables are unnecessary. And I need to build a SQL query with following conditions:

If(@endDate = 0)
Select Id from MyTable where ProductionYear > @startDate
else
Select Id from MyTable where ProductionYear BETWEEN @startDate and @endDate.

How can I build a query with those conditions?

Upvotes: 0

Views: 253

Answers (2)

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31993

you can try by using case when 

  Select Id from MyTable 
    where ProductionYear BETWEEN (case when @startDate>@endDate then @endDate
 else @startDate  end) and 
(case when @startDate>@endDate then @startDate else @endDate end)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269773

You can incorporate this into a single query:

Select Id
from MyTable
where ProductionYear >= @startDate and
      (ProductionYear <= @endDate or @endDate = 0);

Your two queries are inconsistent on whether @startDate is included. BETWEEN includes the comparison values, but > does not.

If you want @startDate to also be optional:

Select Id
from MyTable
where (ProductionYear >= @startDate or @startDate = 0) and
      (ProductionYear <= @endDate or @endDate = 0);

Some additional comments. Calling a "year" a "date" is confusing. Your parameters should probably be called @startYear and @endYear.

These queries are going to result in full table scans of your table. This is probably not a big deal, because the granularity is by year. If the granularity were more refined, you might want to use an index. In that case, perhaps the best approach is dynamic SQL.

Upvotes: 2

Related Questions