Ian Stallings: web log

Tons of Patterns

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.

SQL Server Reporting Services - Multi-Value Params - part 2

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.

<code>

CREATE  FUNCTION StringToBigIntTable (
    @TargetString varchar(8000),
    @SearchChar varchar(1)
    )
    RETURNS @Set TABLE (
        idPos smallint not null identity,
        ExtractedInt bigint null
        )
AS
BEGIN

    SET @TargetString = COALESCE(@TargetString,'')
    DECLARE @SearchCharPos smallint,
            @LastSearchCharPos smallint
    SET @SearchCharPos = 0
    WHILE 1=1
    BEGIN
        SET @LastSearchCharPos = @SearchCharPos
        SET @SearchCharPos = CHARINDEX( @SearchChar, @TargetString, @SearchCharPos + 1 )
        IF @SearchCharPos = 0
        BEGIN
            INSERT @Set( ExtractedInt ) VALUES ( Convert(bigint, SUBSTRING( @TargetString, @LastSearchCharPos + 1, DATALENGTH( @TargetString ) )) )
            BREAK
        END
        ELSE
            INSERT @Set( ExtractedInt ) VALUES ( Convert(bigint, SUBSTRING( @TargetString, @LastSearchCharPos + 1, @SearchCharPos - @LastSearchCharPos - 1 )) )
    END
    RETURN
END


GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

</code>


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:


<code>

SELECT  *
FROM vwExpectedReport
WHERE ProgramID IN (select ExtractedInt from dbo.StringToBigIntTable(@ProgramIDs, ','))
</code>


I hope this helps someone down the road.