Do you trust your users to always pass range search parameters in the correct order? Or do the users trust that the code will take care of a small thing like parameter ordering?
If you have a stored procedure or a script that accepts two parameters to do a range search using a BETWEEN keyword (or maybe with a >= and <=) then it is important to verify that the start expression and end expression are in the correct order. The correct order for numeric values is smaller value before a larger value. The correct order for character values is the dictionary order. A user might pass on the expression values the wrong way around and see no results returned.
The following demonstration has a small check for the correct order of values and to do a quick reorder to fix any issues. To help things a bit more, instead of using generic variable names like @param1 and @param2, they can be made self-documenting by being descriptive like @begin_param and @end_param.
Demonstration with numeric values:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 | declare @DemoTableNumeric table (col1 int ) -- Insert 10 rows in the demo table insert into @DemoTableNumeric (col1) values (1),(2),(3),(4),(5), (6),(7),(8),(9),(10) -- verify data select * from @DemoTableNumeric declare @param1 int , @param2 int -- Assign values -- Note: Param1 > Param2 select @param1 = 7, @param2 = 4 -- The following return zero rows -- because the first expression is -- greater than the second expression -- It is a wrong order of values. select * from @DemoTableNumeric where col1 between @param1 and @param2 select * from @DemoTableNumeric where col1 >= @param1 and col1 <= @param2 -- It is important to verify the expression -- values and reorder them if necessary -- The following IF condition does that if @param1 > @param2 begin declare @ temp int set @ temp = @param1 set @param1 = @param2 set @param2 = @ temp end -- Now both queries return rows select * from @DemoTableNumeric where col1 between @param1 and @param2 select * from @DemoTableNumeric where col1 >= @param1 and col1 <= @param2 |
Demonstration with character values:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 | declare @DemoTableChar table (col1 varchar (10)) insert into @DemoTableChar (col1) values ( 'Alpha' ), ( 'Golf' ), ( 'Kilo' ), ( 'Oscar' ), ( 'Tango' ), ( 'Zulu' ) -- verify data select * from @DemoTableChar declare @param1 varchar (10), @param2 varchar (10) -- Note: Param1 > Param2 select @param1 = 'Tango' , @param2 = 'Golf' -- This returns zero rows -- because the first expression is -- greater than the second expression -- It is a wrong order of values. select * from @DemoTableChar where col1 between @param1 and @param2 select * from @DemoTableChar where col1 >= @param1 and col1 <= @param2 -- It is important to verify the expression -- values and reorder them if necessary if @param1 > @param2 begin declare @ temp varchar (10) set @ temp = @param1 set @param1 = @param2 set @param2 = @ temp end -- Now both queries return rows select * from @DemoTableChar where col1 between @param1 and @param2 select * from @DemoTableChar where col1 >= @param1 and col1 <= @param2 |