Today I came across the PatternShare
website and got stoked. This is a place to share software patterns with others, all licensed under the Creative Commons license. So I thought I'd share. There are a ton of patterns
listed already, including the Gang of Four patterns, Martin Fowler's Enterprise Patterns, Enterprise Integration Patterns (one of my favorite topics as they deal mainly with messaging), and many others. This is a great repository of information for software developers. Chances are what you are working on has already been done, patterns are a way to share that experience, so dig in.
OK, so in my last post
I talked about how SQL server reporting services does not handle multi-value parameters and I would need to work around it. So I thought I'd share my work around for those that might need to do the same.
Basically I ended up writing a User Defined Function and compiling it on the SQL server I was pulling the reporting data from. This function will parse a string using the supplied delimiter and then return a table with an index and a bigint. I needed to return a recordset of bigints for a comparison but you may need another data type.
CREATE FUNCTION StringToBigIntTable (
RETURNS @Set TABLE (
idPos smallint not null identity,
ExtractedInt bigint null
SET @TargetString = COALESCE(@TargetString,'')
DECLARE @SearchCharPos smallint,
SET @SearchCharPos = 0
SET @LastSearchCharPos = @SearchCharPos
SET @SearchCharPos = CHARINDEX( @SearchChar, @TargetString, @SearchCharPos + 1 )
IF @SearchCharPos = 0
INSERT @Set( ExtractedInt ) VALUES ( Convert(bigint, SUBSTRING( @TargetString, @LastSearchCharPos + 1, DATALENGTH( @TargetString ) )) )
INSERT @Set( ExtractedInt ) VALUES ( Convert(bigint, SUBSTRING( @TargetString, @LastSearchCharPos + 1, @SearchCharPos - @LastSearchCharPos - 1 )) )
SET QUOTED_IDENTIFIER OFF
SET ANSI_NULLS ON
Then in my data view for my report (the RDL file) I use the following sql statement , calling my function to parse the parameter and return a recordset of bigints:
WHERE ProgramID IN (select ExtractedInt from dbo.StringToBigIntTable(@ProgramIDs, ',')
I hope this helps someone down the road.