Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Splitting A Parameter From Report Services Expand / Collapse
Author
Message
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: 2 days ago @ 3:16 PM
Points: 58, Visits: 394
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.
Post #457821
Posted Wednesday, February 20, 2008 5:53 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, November 24, 2014 11:02 AM
Points: 2,278, Visits: 3,065
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
Post #457926
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse