Multi-Value Parameter with Stored Procedure

• EjSQLme

  SSCrazy

  Points: 2172

  More actions

  July 30, 2010 at 6:38 am

  #238297

  Hi,

  I googled this issue and I was not able to come up with any solution... not sure what I'm doing wrong. Whatever I do, either I get an error (different error based what I try) or only the first value is displayed. This is for SSRS 2005

  I'm using the Stored Procedure below and in my Dataset I only have the SP name and the Command type is SP. In the Dataset Parameters, the Name is @ClientID and the value is =JOIN(Parameters!ClientID.Value, ",")

  The Report Parameters has Multi-Value and Allow blank value selected... in the Available Values, From query is checked and I use a simple query that would pull the ClientID and the ClientName from another DataSet:

  SELECT DISTINCT KC.ClientID, KC.ClientCode + ' - ' + KC.ClientName as ClientName

  from CLIENTS KC

  I Found the Split Function in WHERE clause from some site (http://www.bidn.com/blogs/mikedavis/ssis/360/multi-value-parameters-from-stored-procedures-in-ssrs)

  ALTER PROCEDURE [dbo].[spr_ServerInfoByClients]

  @ClientID nvarchar(4)

  AS

  BEGIN

  -- SET NOCOUNT ON added to prevent extra result sets from

  -- interfering with SELECT statements.

  SET NOCOUNT ON;

  SELECT KC.ClientID, KC.ClientCode, KC.ClientName, KS.ServerName, KS.ServerDesc, KS.ServerIPAddress, KS.ServerType

  FROM CLIENTS KC INNER JOIN SERVERS KS ON (KC.ClientID = KS.ClientID)

  WHERE

  KS.ClientID IN (SELECT Item FROM Split(@ClientID, ','))

  ORDER BY KS.ClientID

  END

  Thanks,

• EjSQLme

  SSCrazy

  Points: 2172

  More actions

  July 30, 2010 at 7:28 am

  #1200311

  Please disregard... i found the answer. I needed to change the character length to Max.