Being extensively involved in report development projects, I've come across numerous occasions where requirements asks for the ability to implementing a logic to filter data in stored procedures based on multiple selection parameter value. In these cases the values will be send in comma delimited format to the procedure. The straight forward ways of implementing the logic are
1. Use a string parsing udf to parse out values as a table and then add a join to that.
2. Use LIKE based search condition in WHERE looking for value pattern within the delimited list
I'm explaining another way of implementing the search logic using logical operators here.
For this illustration, I'm taking an example of case where we've a calendar table and requirement is to create a search procedure that accepts multiple day values and lists out all dates falling as the day.
The table will look like below. For simplicity I've added only relevant columns ie Dt and DyNm in the table
As you see from above, the table will have all dates starting from 1 Jan 1753 to 31 Dec 2099
The procedure will look like below
The search logic is implemented by first doing bit wise OR operation for the bits based on selected values from the @DayBit table. This is then used in the final select query and joined to the main table on DayName and then boolean AND operation is done and checked for > 0 to ensure only records whose bit values fall within selected days are filtered.
If you check the output for above code it will list out dates which falls in the days included in the parameter value list.
This is a good method which can be used for filtering table based on multivalued parameter list. As this uses logical operator for filtering it will perform much better compared to applying string pattern search directly over the entire table especially when the table data volume is huge.
1. Use a string parsing udf to parse out values as a table and then add a join to that.
2. Use LIKE based search condition in WHERE looking for value pattern within the delimited list
I'm explaining another way of implementing the search logic using logical operators here.
For this illustration, I'm taking an example of case where we've a calendar table and requirement is to create a search procedure that accepts multiple day values and lists out all dates falling as the day.
The table will look like below. For simplicity I've added only relevant columns ie Dt and DyNm in the table
The procedure will look like below
CREATE PROC CalendarDayListThe logic conists of creating a table with bit values corresponding to each of the days, Bit values correspond to powers of 2 like 2^0,2^1,...
@DayList varchar(5000)
AS
declare @selectbit int = 1
declare @DayBit table
(
DayValue varchar(15),
BitValue int
)
insert @DayBit
values ('Monday',1),
('Tuesday',2),
('Wednesday',4),
('Thursday',8),
('Friday',16),
('Saturday',32),
('Sunday',64)
select @selectbit=@selectbit |BitValue
from @DayBit
where ',' + @DayList + ',' LIKE '%,' + DayValue + ',%'
SELECT d.*
FROM DateDim d
JOIN @DayBit db
ON db.DayValue = d.DyNm
WHERE db.BitValue & @selectbit > 0
GO
The search logic is implemented by first doing bit wise OR operation for the bits based on selected values from the @DayBit table. This is then used in the final select query and joined to the main table on DayName and then boolean AND operation is done and checked for > 0 to ensure only records whose bit values fall within selected days are filtered.
If you check the output for above code it will list out dates which falls in the days included in the parameter value list.
This is a good method which can be used for filtering table based on multivalued parameter list. As this uses logical operator for filtering it will perform much better compared to applying string pattern search directly over the entire table especially when the table data volume is huge.
No comments:
Post a Comment