VARCHAR
CREATE PROC dbo.uspMultiValueTest ( @Input varchar(1000) -- Make sure this size is big enough! ) AS BEGIN /*--- Declarations ---*/ declare @doc xml ,@s varchar(1000) ,@Delimiter varchar(2) /*--- @Values table ---*/ /* This table will have one row for each value in the @Input parameter, which holds all the value from the multi-valued report parameter */ declare @Values table (ID VARCHAR(100)) /*--- Inits ---*/ select @Delimiter = ',' ,@s = replace(@Input, '&', '&') ,@s = '<x><v>' + replace(@s, @Delimiter, '</v></x><x><v>') + '</v></x>' ,@doc = convert(xml, @s) /*--- Load @Values table ---*/ insert into @Values (ID) select v = T.c.value('v[1]', 'varchar(100)') from @doc.nodes('x') T(c) /*--- Main Query ---*/ SELECT result = Name FROM dbo.Facilities WHERE 1=1 /* Filter by the multi-value parameter */ AND FacilityId IN (SELECT ID FROM @Values) end
Edit Dataset Button
=JOIN(Parameters!MyReportParameter.Value,",")
Dataset Properties Dialog, Parameters tab
ScrewTurn Wiki version 3.0.1.400. Some of the icons created by FamFamFam. Except where noted, all contents Copyright © 1999-2024, Patrick Jasinski.