Jibin Balachandran Jibin Balachandran - 2 months ago 10
SQL Question

Can we use a CASE in BETWEEN

I know that we can use BETWEEN inside a CASE. But I have a situation where I want to do the other way round. I know that we can't return more than one value from case statement.But how to achieve the below situation??

select * from #Results
where Num Between
case when @StartIndex>0 then
((@StartIndex-1) * @PageCount))+ 1)) AND (@StartIndex * @PageCount)
else
((@StartIndex-1) * @PageCount)+ 1) AND (((@StartIndex-1) * @PageCount)+ 1)

Answer

You need AND/OR logic

SELECT * 
FROM   #results 
WHERE  ( num BETWEEN ( ( @StartIndex - 1 ) * @PageCount ) + 1 AND @StartIndex * @PageCount 
         AND @StartIndex > 0 ) 
        OR ( num BETWEEN ( ( @StartIndex - 1 ) * @PageCount ) + 1 AND ( ( @StartIndex - 1 ) * @PageCount ) + 1 
             AND @StartIndex <= 0 ) 
Comments