Regarding:
Multi-Select Parameters for Reporting Services
Regular Columnist : Adriaan Davel
Posted: 08/13/2007
Our solution:
Create a table function to parse the values into a table.
Usage:
In a stored procedure called by SSRS, pass the multi-select values into this function to return a table of key/value pairs.
ie:
select * from fnCSV_Parse (',' , 'ca,de,ny,wa')
yeilds:
tblID Keys
-----------------
1 ca
2 de
3 ny
4 wa
This way you can have a query that simply joins to the function.
Select s.*
from state_codes as s
Inner Join dbo.fnCSV_Parse (',' , 'ca,de,ny,wa') as b
on s.State_Code = b.Keys
--or to get an indexed join
declare @s_codes table
(State_Code char(6) primary key
)
insert @s_codes(State_Code)
select Keys from fnCSV_Parse (',' , 'ca,de,ny,wa')
Select s.*
from state_codes as s
Inner Join @s_codes as b
on s.State_Code = b.State_Code
Performance has been great against multi million row tables.
A Normalization Primer
For most DBAs, normalization is an understood concept, a bread and butter bit of knowledge. However, it is not at all unusual to review a database design by a development group for an OLTP (OnLine Transaction Processing) environment and find that the schema chosen is anything but properly normalized. This article by Brian Kelley will give you the core knowledge to data model.
2003-01-13
18,604 reads