SQLErudition.com

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