How to have a case block in query where condition

Transact-SQL
1

I want to use a case when block in the where condition: I am trying to use the below not working, is it possible to use within where condition. Thanks a lot for the helpful info.

where rm.ProgID <> 70
and rm.progid = @progid
and CASE @progid
WHEN '' THEN '1=1'
ELSE rm.progid = @progid
END
and CONVERT(varchar(10), rm.rmloggeddate, 101) between CONVERT(varchar(10), @startdate, 101) and CONVERT(varchar(10), @enddate, 101)
order by rm.sequenceno,MR.step;

2

I fixed the date conditions as well, no extra charge :relaxed:

where rm.ProgID <> 70
and (@progid = '' or rm.progid = @progid)
and rm.rmloggeddate >= CONVERT(varchar(8), @startdate, 112) 
and rm.rmloggeddate < CONVERT(varchar(8), dateadd(day, 1, @enddate), 112)
order by rm.sequenceno,MR.step;
3

Thank you Scott.

4

You're welcome! If that table is clustered on rmloggeddate, as it almost certainly should be, you should see drastically better performance from the new date comparisons.

5

Do you need that, or could you just compare against @StartDate?

Or use DATEADD(Day, DATEDIFF(Day, ... if it was necessary to truncate a TIME portion off a DATETIME datatype, or similar.

Just curious :slightly_smiling:

6

Yes, it was needed to keep compatibility with the earlier code. I have no idea what data type either of the values is. But the safest comparison to any smalldatetime/date/datetime/datetime2 column format is varchar, because SQL will implicitly convert it to the matching data type. For two variable conversions, I don't worry about efficiency just accuracy :slightly_smiling:.

7

I'm figuring that (given your clustered index assumption) your query will be sargable. Given my limited knowledge I only ever assume that a query will be sargable if I provide oven-ready @Parameters and no CONVERTs etc. But that is only because I know if I do that it will be, and I don't then have to check whether it does / doesn't :slightly_smiling: but if I can get away with CONVERT(varchar(8) to get a date / datetime to "YYYYMMDD" and have the resulting query be sargable it seems a lot less work that the whole DATEADD(Day, DATEDIFF(Day shenanigans !

8

You can use CONVERTs, concatenation, calcs, etc., on the @variable / literal side of the comparison, and that won't affect "sargability", since SQL will fully resolve that before running the query anyway.

I'll admit SQL Server now compares datetime literals to a date column but avoids implicitly converting the column to datetime (which has a higher data precedence). MS has put such performance improvements into the engine itself. But, I still support some SQL2005 instances (grrr!) and idk for sure if that engine has those improvements. Either way, it's safe to use varchar and let SQL implicitly convert it for static values. Again, since it's done only once, during one of the "pre-compile" phases, it's not enough overhead to measure.

1 Like
9

Quite so. That's the whole point of writing it that way :slightly_smiling:

1 Like