CODEWITHSUNDEEP
sqlsybasewhere-clause
LPP
LPP

Reputation: 77

SQL conditional where clause with single date and date range

Using Sybase.

I have a store procedure accepting multiple parameters.

@param1 and @date1 will always have values, but @param2, @param3, and @date2 are optional.

I have written something like this. 

(@param1, @param2, @param2, @date1, @date2)

SELECT * FROM table
WHERE col1 = @param1
AND col2 = COALESCE(NULLIF(@param2,''), col2)
AND col3 = COALESCE(NULLIF(@param3,''), col3)
AND
IF(@date2 is empty)
  col4 = @date1
ELSE
  col4 IS BETWEEN @date1 AND @date2

Looking help with writing the last part. If condition with dates.

Upvotes: 2

Views: 656

Answers (2)

Adam Leszczyński
Adam Leszczyński

Reputation: 1161

The most elegant way would be to write the last part as:

AND col4 between @date1 and coalesce(@date2, @date1)

This way if @date2 is null the condition would work like:

AND col4 between @date1 and @date1

which is equal to:

AND col4 >= @date1 and col4 <= @date1

which is equal to:

AND col4 = @date1

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269973

if doesn't belong there. And it isn't needed:

SELECT *
FROM table
WHERE col1 = @param1 AND
     col2 = COALESCE(NULLIF(@param2,''), col2) AND
     col3 = COALESCE(NULLIF(@param3,''), col3) AND
     ( (@date2 IS NULL AND col4 = @date1) OR
       col4 IS BETWEEN @date1 AND @date2
     )

Upvotes: 1

Related Questions