[Intro]
Using SSRS multi-value parameters with stored procedures can be a bit of a pain. Multi-Valued parameters are sent to Stored Procedures as a comma-delimited string. What follows is the method I use to write and debug stored procedures for SSRS reports.The web is full of tutorials on handling multi-valued parameters in stored procedures. What I’m hoping to show is a method to set them up so that you can easily debug the stored procedure later on down the road.
[Background]
I found some code on SQLServerCentral.com that does a good job of splitting a comma-delimited string into a table of individual values. I wish I could credit the person who originally posted this idea, but there are lots of variations of this code floating around, and I modified the version I originally found, so please accept my apologies if this looks familiar.This function will break down a comma-delimited string into a table of individual values. The one I’m posting here returns a table of ints, but I have others that return a table of other values (i.e. strings):
Here’s a simple example of a stored procedure using this function:
----------------------------------------------------
-- [dbo].[fnc_split_int]
-- Parse a comma delimited string and insert the values into a table variable.
-- Useful for sending a comma-delimited string parameter to a stored proc that will
-- use it in a where IN clause.
--
-- Ex:
-- select * from customers
-- will not work: Where customer_id in (@parameter)
-- will work : Where customer_id in (select item from fnc_split_string(@parameter,','))
---------------------------------------------------------
ALTER FUNCTION [dbo].[fnc_split_int](
@list varchar(8000),
@Delimiter VARCHAR(10) = ','
)
RETURNS @tablevalues TABLE (
item int
)
AS
BEGIN
DECLARE @P_item varchar(255)
WHILE (DATALENGTH(@list) > 0)
BEGIN
IF CHARINDEX(@Delimiter,@List) > 0
BEGIN
SELECT @p_Item = SUBSTRING(@List,1,(CHARINDEX(@Delimiter,@List)-1))
SELECT @List = SUBSTRING(@List,(CHARINDEX(@Delimiter,@List) + DATALENGTH(@Delimiter)),DATALENGTH(@List))
END
ELSE
BEGIN
SELECT @p_Item = @List
SELECT @List = NULL
END
INSERT INTO @tablevalues
SELECT Item = convert(int, LTRIM(RTRIM(@p_Item)))
END
RETURN
END
create proc dbo.salesinfo (
@customers varchar(max)
)
as
Select * from dbo.sales
where customer_id in ( select item from fnc_split_int(@customers, ',') )
[Using Table Variables]
The method above works well, but can get kind of annoying if you ever have to debug the stored procedure. The stored procedure could be huge, and you might not care too much about which customers are displayed. However it now requires a comma-delimited string of customers anytime you want to run some tests.
To keep me from losing my mind, I add a few table variables in my stored procedure that are designed to handle the multi-value SSRS parameters.
This is probably best explained in an example:
create proc rpt.salesinfo (
@customers varchar(max) = null --set to null by default
)
as
--Build local customer table
declare @customerlist table (
customer_id int
)
if @customers is null
begin
--Load all customers (null)
insert into @customerlist
select customer_id from customers
end
else
begin
--Load only those customers chosen in @customers
insert into @customerlist
select item from fnc_split_string(@customers, ',')
end
--report query (using @customerlist)
select * from sales
where customer_id in (select customer_id from @customerlist)
Notes:
- It might seem a little excessive to build a local table of values when you already have those values in comma-delimited form. But using this method has saved me a lot of headaches when I’m trying to debug something in the stored procedure.
- I could use a join instead of putting the @customerlist table in the where string. However, I’ve found that performance hasn’t really been an issue for our setup. I also think it’s a little easier to read – a new developer doesn’t have to worry about finding a join that is only used to limit the resultset. From what I remember, the server does a lot to optimize queries anyway, so things like this usually do not matter as much.
[Final Thoughts]
This example shows a method I use to test and debug stored procedures for SSRS reports that use multi-valued parameters. There isn’t any ground breaking functionality introduced, just a bunch of smaller things strung together to hopefully make life easier. I should mention there are other ways of doing this, some might be better than this one. At the time of this writing I was only using SQL Server 2005.Please feel free to ask questions or suggest improvements.
