T-SQL handling BETWEEN with two parameters

I'm trying to use between to identify when a value falls between 2 numbers. For example
select rec from xxx where 100 between 50 and 200 This returns the rows however if I flip the numbers
select rec from xxx where 100 between 200 and 50 it fails

End User will be entering beginning and ending sequence numbers its possible that the entries will be reversed where the larger number is actually the beginning sequence number So I cant just flip the numbers to satisfy the rule.
LVL 1
bensonwalkerAsked:
Who is Participating?
 
Scott PletcherSenior DBACommented:
select rec from xxx
where 100|<column>
    between case when @value1 < @value2 THEN @value1 ELSE @value2 END
    and case when @value1 < @value2 THEN @value2 ELSE @value1 END
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.