December 20, 2005 06:20 PM

Reporting Services and Multivalued Parameters

A few tricks coax your stored procedures into accepting a delimited list
Rating: (0)
SQL Server Magazine
InstantDoc ID #48596
Watching a product grow over the years is kind of like watching a child grow to maturity, adding skills and capabilities to his or her repertoire. I've been working with SQL Server Reporting Services since its infancy as a bouncing baby beta born into SQL Server 2000. At the time, it could hold its own with peers such as Crystal Reports, and my company was impressed enough with its capabilities that we adopted it with dreams of making it our de facto Web-based reporting solution—and we hav...

...This article is for paid Professional Members only.

Already a Professional Member? Please log in now:

NOT A PROFESSIONAL MEMBER? YOU CHOOSE:

Professional Membership

Monthly

Annual

VIP Membership

Monthly

Annual

Add a Comment

Another scenario I forgot to mention is when the parameter being passed is a GUID. Since guids are by definition a fixed length when part of a string consisting of an array of guids, there is an even more efficient parsing method credited by Sommarskog to Ken Henderson. Here is my implementation of the guid parser using nvarchar(MAX) to allow a basically unlimited number of GUID's:

CREATE FUNCTION dbo.fnParseGUIDs(@str nvarchar(MAX))
RETURNS @ParsedValues TABLE (pk int, GUID uniqueidentifier)
AS

BEGIN
INSERT INTO @ParsedValues (pk, GUID)
SELECT Number
, CAST(LEFT(SUBSTRING(@str, 37 * (Number - 1) + 1, 37),36) AS uniqueidentifier)
FROM Numbers
WHERE Number <= DATALENGTH(@str) / (37 * 2)
+ CASE DATALENGTH(@str) % 37 WHEN 0 THEN 0 ELSE 1 END
RETURN
END

Happy Holidays,

Tim

timoverlund@hotmail.com 12/23/2005 12:56:58 PM


We have definitely run into this issue on our current project. However, I don't believe the UDF presented is particularly efficient as it basically uses a WHILE loop to do cursor-like row-based processing of the array. Here is an alternative I borrowed from Erland Sommarskog's excellent article 'Arrays and Lists in SQL Server' (http://www.sommarskog.se/arrays-in-sql.html#overview). It uses a prepopulated Numbers table which is simply an integer column with values from 1-8000. In order to get a key for ordering the list, I originally added an IDENTITY column, which I replaced with the SQL Server 2005 ROW_NUMBER() OVER syntax.

CREATE FUNCTION dbo.fnParseDelimitedList (@list varchar(7998), @delimiter char(1))
RETURNS @ParsedValues TABLE (pk int, Value varchar(7997))
AS

BEGIN
INSERT INTO @ParsedValues (pk, Value)
SELECT ROW_NUMBER() OVER(ORDER BY Number), SUBSTRING(@delimiter + @list + @delimiter, Number + 1,
CHARINDEX(@delimiter, @delimiter + @list + @delimiter, Number + 1) - Number - 1)
AS Value
FROM dbo.Numbers
WHERE Number <= LEN(@delimiter + @list + @delimiter) - 1
AND SUBSTRING(@delimiter + @list + @delimiter, Number, 1) = @delimiter
RETURN
END

Tim Overlund, MCDBA, MCSD.NET
Database Practice
Avanade, Inc

timoverlund@hotmail.com 12/23/2005 12:37:55 PM


You must log on before posting a comment.

Are you a new visitor? Register Here