Monday, April 4, 2011

SQL Reporting Services Multi Value Parameter

The following T-SQL user defind function converts comma delimited string to a table (
usage described at http://social.msdn.microsoft.com/Forums/en/sqlreportingservices/thread/c02370b5-aeda-47ec-a3a8-43b2ec1e6c26):

SET QUOTED_IDENTIFIER ON
GO

SET
ANSI_NULLS ON
GO

PRINT
'drop function dbo.udfMulti_Value'
GO

if
exists (select * from dbo.sysobjects
where id = object_id(N'dbo.udfMulti_Value') and xtype in (N'FN', N'IF', N'TF'))
drop function dbo.udfMulti_Value
GO

PRINT
'CREATE FUNCTION dbo.udfMulti_Value'
GO

/******************************************************************************
NAME: udfMulti_Value
PURPOSE:

REVISIONS:

$Log$

Ver Date Author Description
--------- ---------- --------------- ------------------------------------

******************************************************************************/

CREATE FUNCTION dbo.udfMulti_Value(@code NVARCHAR(MAX))
RETURNS @temp table(Code NVARCHAR(MAX))
AS

BEGIN

If ISNULL(@code ,'') = '' BEGIN
SET @code = 'ALL' END
ELSE
WHILE LEN(@code ) > 0 BEGIN
IF PATINDEX('%,%', @code ) > 0 BEGIN
INSERT INTO @temp VALUES(RTRIM(SUBSTRING(@code ,1,PATINDEX('%,%', @code )-1)))
SET @code = LTRIM(SUBSTRING(@code ,PATINDEX('%,%', @code )+1,1024)) END
ELSE BEGIN
INSERT INTO @temp VALUES(LTRIM(RTRIM(@code )))
BREAK
END

END

RETURN

END

GO

SET
QUOTED_IDENTIFIER OFF
GO

SET
ANSI_NULLS ON
GO

If you find this script useful please donate generously.

No comments:

Post a Comment