SELECT * FROM #Fundraising
WHERE Fund IN (@Fund)

ALTER FUNCTION [dbo].[fn_SplitString]
(
	@CSVString 	NVARCHAR(MAX),
	@Delimiter	NVARCHAR(1)
)
RETURNS @tbl TABLE (Result NVARCHAR(MAX))
AS
BEGIN
DECLARE 
	@i INT,
	@j INT
	SELECT 	@i = 1
	WHILE @i <= LEN(@CSVString)
	BEGIN
		SELECT	@j = CHARINDEX(@Delimiter, @CSVString, @i)
		IF @j = 0
		BEGIN
			SELECT @j = LEN(@CSVString) + 1
		END
		INSERT @tbl SELECT SUBSTRING(@CSVString, @i, @j - @i)
		SELECT	@i = @j + LEN(@Delimiter)
	END
	
	UPDATE @tbl
	SET Result = '''' + Result + '''' + ','
	RETURN

END

SELECT * FROM #Fundraising
WHERE Fund IN (SELECT * FROM fn_SplitString(@Fund,','))

quote:

---

Here's a bit of background. I have a SSRS report running that has the ability for users to select multiple values. When SSRS passes this parameter to my SQL sp, it passes a single string that is enclosed in quotes (as oposed to enclosing each value in this string in quotes, its a shortcoming of SSRS)

---

SELECT * FROM Calls WHERE a_number IN (N'93018391',N'36372564')
(Numbers are created using a random number generator)

SELECT * FROM Calls WHERE a_number IN (@Numbers)

quote:

---

Originally posted by Lumbago

quote:

---

Here's a bit of background. I have a SSRS report running that has the ability for users to select multiple values. When SSRS passes this parameter to my SQL sp, it passes a single string that is enclosed in quotes (as oposed to enclosing each value in this string in quotes, its a shortcoming of SSRS)

---

This is not correct. I captured the resulting SQL from a SSRS report that accepts multiple values using Profiler and the query that is sent to the database looks like this:

SELECT * FROM Calls WHERE a_number IN (N'93018391',N'36372564')
(Numbers are created using a random number generator)

---

exec sp_executesql N'EXEC sp_AllFundraisingPayments @Fund',N'@Fund nvarchar(37)',@Fund=N'Annual Giving 2008,Annual Giving 2009'

quote:

---

Msg 116, Level 16, State 1, Procedure sp_AllFundraisingPayments, Line 84
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

---

ALTER FUNCTION dbo.fnParseList
(
	@Delimiter CHAR,
	@Text TEXT
)
RETURNS @Result TABLE (RowID SMALLINT IDENTITY(1, 1) PRIMARY KEY, Data VARCHAR(8000))
AS

BEGIN
	DECLARE	@NextPos INT,
		@LastPos INT

	SELECT	@NextPos = CHARINDEX(@Delimiter, @Text, 1),
		@LastPos = 0

	WHILE @NextPos > 0
		BEGIN
			INSERT	@Result
				(
					Data
				)
			SELECT	SUBSTRING(@Text, @LastPos + 1, @NextPos - @LastPos - 1)

			SELECT	@LastPos = @NextPos,
				@NextPos = CHARINDEX(@Delimiter, @Text, @NextPos + 1)
		END

	IF @NextPos <= @LastPos
		INSERT	@Result
			(
				Data
			)
		SELECT	SUBSTRING(@Text, @LastPos + 1, DATALENGTH(@Text) - @LastPos)

	RETURN
END

	SELECT * FROM #Fundraising
	WHERE Fund IN (SELECT * FROM fnParseList(',',@Fund))

SELECT * FROM #Fundraising
WHERE Fund IN (SELECT Data FROM fnParseList(',',@Fund))

SELECT      fr.*
FROM        #Fundraising AS fr
INNER JOIN  (
                SELECT  DISTINCT
                        Data
                FROM    dbo.fnParseList(',', @Fund)
            ) AS x ON x.Data = fr.Fund

quote:

---

That's strange, here's the output from the trace I ran on the report with Profiler:

exec sp_executesql N'EXEC sp_AllFundraisingPayments @Fund',N'@Fund nvarchar(37)',@Fund=N'Annual Giving 2008,Annual Giving 2009'

---

quote:

---

Single and Multivalued Parameters
You can define a multivalued parameter by selecting the Multivalued option in the Report Parameters dialog box. If this checkbox is not selected, the parameter is single-valued. Specifying the Multivalued option adds checkboxes to the available values in a parameter drop-down list in the published report. Users can check the values they want (for example, in the Sales Reason Comparisons sample report, users can select multiple products to view the combined sales data for those products). A (Select All) option is added to the available values list automatically for parameters that are defined as multivalued.

When specifying the available values, you can provide a static list of strings or use a query to retrieve available values from a data source. A multivalued parameter must include at least one value. Null values are not allowed.

Writing Queries that Map to Multivalued Report Parameters
You can define a multivalued parameter for any report parameter that you create. However, if you want to pass multiple parameter values back to a query, the following requirements must be satisfied:

The data source must be SQL Server, Oracle, or Analysis Services.

The data source cannot be a stored procedure. Reporting Services does not support passing a multivalued parameter array to a stored procedure.

---