Splitting A Parameter From Report Services

• Stylez

  SSCrazy

  Points: 2195

  More actions

  February 19, 2008 at 10:02 pm

  #186901

  Hi To All

  I have a Paramater From Reporting Services Named Classification in which it is a Multi Valued Paramater. i am trying to pass it to sql server to process and return a value but it doesnt seem to work

  my parameter looks like this

  declare @classification varchar(40)

  after it is passed by the reporting services it will look something like this

  set @classification = ('Active,Terminated')

  i would like to use this in a Select statement named

  select * from Table name where columnname in (@classification)

  but everytime i pass that value(@classification) my query results return nothing. do you have a function to split the

  @classification = ('Active,Terminated') variable

  into

  @classification = ('Active','Terminated')

  i will now use this to create a select statement that would look like this

  select * from table where columnname in ('Active','Terminated')

  any form of assistance is very much appreciated

  Best Regards,

  N.O.E.L.

• Adam Haines

  SSC-Insane

  Points: 23217

  More actions

  February 20, 2008 at 5:53 am

  #780155

  Well your options are to parse the string directly to an inner join or to create a udf that returns a table, that you will inner join.

  DECLARE @x XML

  SET @x = '<i>' + REPLACE(@classification , ',', '</i><i>') + '</i>'

  SELECT *

  FROM MyTable a

  INNER JOIN

  (SELECT x.i.value('.', 'VARCHAR(7)') AS [Classification]

  FROM @x.nodes('//i') x(i)) b

  ON a.Classification = b.Classification

  Like I said, your other option is to drop the xml into a function and inner join your table on the function.

  ---

  
  My blog: http://jahaines.blogspot.com