declare @test-2 int

set @test-2=-2

select case when @test-2 between -1 and -10 then 'Order does not matter'

else 'Order matters' end as Conclusion

It would seem to me that order shouldn't matter. As it is, if you are using parameters for the comparison and don't know which will be larger, you would need the below:

select case when (@test >= @compare1 and @test-2 <= @compare2) OR (@test >= @compare2 and @test-2 <= @compare1) then 'Sheesh, what a long way around.' end

I saw it was from Hugo, and checked carefully for tricks. Not carefully enough. 😀