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:
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:
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
