Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Splitting A Parameter From Report Services Expand / Collapse
Posted Tuesday, February 19, 2008 10:02 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Today @ 4:38 PM
Points: 74, Visits: 529
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


@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,

Post #457821
Posted Wednesday, February 20, 2008 5:53 AM


Group: General Forum Members
Last Login: Friday, February 26, 2016 10:23 AM
Points: 2,280, Visits: 3,135
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.

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

FROM MyTable a
(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:
Post #457926
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse