資料集的查詢類型設為預存程序時，使用查詢參數的小技巧
當我們在設計報表時，可以透過SSRS所提供的功能讓使用者以複選的方式來選擇他所要的參數值，SSRS會將使用者所選的內容根據報表參數的資料型態，自動以逗點為分隔符號串起來。
假設我們建立兩個資料集，其中DataSet1取得Country選項，作為DataSet2的查詢參數。
【DataSet1 TSQL】
1: select distinct Country
2: from Customers
【DataSet2 TSQL】
1: select *
2: from Customers
3: where Country IN (@Country)
接著設定報表參數，將Country設定為允許多個值（如下圖左），並將可用的值選擇從查詢取得，來源是DataSet1，值欄位和標籤欄位都選擇Country（如下圖右）。
再來從工具箱拉一個資料表，分別設定Country、City及CompanyName至資料欄（如下圖左），預覽報表前先開啟SQL Server Profiler以錄製報表執行時SSRS執行了那些SQL敘述。
假設預覽報表時選擇全選（如上圖中），由上圖右的SQL Server Profiler可見SSRS幫我們執行下列的TSQL敘述，每個在報表預覽所勾選的Country會自動被轉成一個一個查詢參數，並用逗點分隔開來。
1: select *
2: from Customers
3: where Country IN (N'Argentina',N'Austria',N'Belgium',N'Brazil',N'Canada',N'Denmark',N'Finland',N'France'
4: ,N'Germany',N'Ireland',N'Italy',N'Mexico',N'Norway',N'Poland',N'Portugal'
5: ,N'Spain',N'Sweden',N'Switzerland',N'UK',N'USA',N'Venezuela')
若將DataSet2的查詢類型改為Strored Procedure，並選取預存程序名稱為usp_GetCustomers（如下列程式碼及下圖）。
1: CREATE PROCEDURE usp_GetCustomers
2: (
3: @Country NVARCHAR(1024)
4: )
5: AS
6: SELECT *
7: FROM Customers
8: WHERE Country IN(@Country)
9:
10: GO
再次預覽報表，一樣是全選所有國家，透過SQL Server Profiler錄製出下列的TSQL敘述（下圖左），從下圖右發現一筆資料都沒有，原因在於資料集使用Stored Procedure查詢資料時透過報表參數轉換成查詢參數並不會將每個使用者所選取的項目會被彙整成一個參數，因沒有任何一筆資料的Country等於【Argentina, Austria, Belgium, Brazil, Canada, Denmark, Finland, France, Germany, Ireland, Italy, Mexico, Norway, Poland, Portugal, Spain, Sweden, Switzerland, UK, USA, Venezuela】所以報表的執行結果是空的。
1: exec usp_GetCustomers @Country=N'Argentina,Austria,Belgium,Brazil,Canada
2: ,Denmark,Finland,France,Germany,Ireland,Italy,Mexico
3: ,Norway,Poland,Portugal,Spain,Sweden,Switzerland,UK,USA,Venezuela'
感謝百敬老師提供的寶貴資訊，得知上述的情況必須自己寫程式轉換，因此筆者寫了一個SQL Server的Function，把報表參數轉成每個國家是獨立的字串，程式碼如下：
1: IF EXISTS (
2: SELECT *
3: FROM sys.objects
4: WHERE type = 'FN'
5: AND name = 'ufn_ReConfigList'
6: )
7: DROP FUNCTION ufn_ReConfigList
8:
9: GO
10:
11: CREATE FUNCTION dbo.ufn_ReConfigList
12: (
13: @List VARCHAR(1024)
14: )
15: RETURNS VARCHAR(1024)
16: AS
17: BEGIN
18: IF @List = '' RETURN ''
19:
20: DECLARE @Delimiter CHAR(1) = ','
21: DECLARE @idx INT = 0
22: DECLARE @Expression VARCHAR(1024) = ''
23: SET @idx = CHARINDEX(@Delimiter,@List)
24:
25: WHILE @idx > 0
26: BEGIN
27: IF @Expression = ''
28: SET @Expression = '''' + SUBSTRING(@List,1,@idx - 1) + ''''
29: ELSE
30: SET @Expression = @Expression + ',''' + SUBSTRING(@List,1,@idx - 1) + ''''
31: SET @List = SUBSTRING(@List,@idx + 1,LEN(@List) - @idx)
32: SET @idx = CHARINDEX(@Delimiter,@List)
33: END
34:
35: IF @Expression = ''
36: SET @Expression = '''' + @List + ''''
37: ELSE
38: SET @Expression = @Expression + ',''' + @List + ''''
39:
40: RETURN @Expression
41: END
42:
43: GO
44:
最後將usp_GetCustomers改為下列程式碼：
1: ALTER PROCEDURE usp_GetCustomers
2: (
3: @Country NVARCHAR(1024)
4: )
5: AS
6:
7: CREATE TABLE #Customers
8: (
9: [CustomerID] [nchar](5) NOT NULL,
10: [CompanyName] [nvarchar](40) NOT NULL,
11: [ContactName] [nvarchar](30) NULL,
12: [ContactTitle] [nvarchar](30) NULL,
13: [Address] [nvarchar](60) NULL,
14: [City] [nvarchar](15) NULL,
15: [Region] [nvarchar](15) NULL,
16: [PostalCode] [nvarchar](10) NULL,
17: [Country] [nvarchar](15) NULL,
18: [Phone] [nvarchar](24) NULL,
19: [Fax] [nvarchar](24) NULL
20: )
21:
22: DECLARE @Expression NVARCHAR(1024) = N'INSERT INTO #Customers
23: SELECT * FROM Customers WHERE Country IN (' + dbo.ufn_ReConfigList(@Country) + ')'
24: EXEC(@Expression)
25:
26: SELECT *
27: FROM #Customers
28: GO
接著依照之前的實驗，預覽報表一樣全選所有國家，透過SQL Server Profiler查看SSRS所執行的TSQL敘述（如下圖左），如此一來查詢結果可以正確顯示（如下圖右）。
【SQL Server Profiler所錄製的TSQL敘述】
1: exec usp_GetCustomers @Country=N'Argentina,Austria,Belgium,Brazil,Canada,Denmark,Finland
2: ,France,Germany,Ireland,Italy,Mexico,Norway,Poland,Portugal,Spain
3: ,Sweden,Switzerland,UK,USA,Venezuela'
