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...

You must be a paid Professional Member to access this entire article.

Already a Professional Member? Please log in now:

NOT A PROFESSIONAL MEMBER? YOU CHOOSE:

Monthly or Annual

Professional Membership

VIP Membership

Compare Member Benefits

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

Tim12/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

Tim12/23/2005 12:37:55 PM


You must log on before posting a comment.

Are you a new visitor? Register Here

Rename Virtual Server (Cluster)

I am going to rename a clustered instance (default).1. Change SQL Network Name2. Take offline3. Bring online4. Flush DNS, Cache5. Test failover6. Rebu...222-96209

GOOGLE LINKS
SPONSORED LINKS
FEATURED LINKS