Link to home
Start Free TrialLog in
Avatar of parpaa
parpaa

asked on

Mutli values to a parameter

I am using a std proc which has 4 parameters (PFA) in my ssrs report.

Now the problem is, when I try to pass multi values to @physicianName- it does not return anything.

I guess the problem is with the comma in the physician name. I had attached the sample data of this table.

Now when the proc exec
-Exec usp_xxxx 'DBname','ABBAS, SHAHIDA, ABBASI, ARSHIA',2012-09-13,2011-09-13

Hope this make sense
Physician-Perform.sql
Physcian-Sample-data.txt
Avatar of DcpKing
DcpKing
Flag of United States of America image

You're using
in ('''+@PhysicianName+''')
with
'ABBAS, SHAHIDA, ABBASI, ARSHIA'
giving
in ('ABBAS, SHAHIDA, ABBASI, ARSHIA')

which is syntactically correct but symantically not.

You need to provide:
'ABBAS, SHAHIDA', 'ABBASI, ARSHIA'
Avatar of parpaa
parpaa

ASKER

Thanks DCPKing for quick response. How would I get this?
SOLUTION
Avatar of SThaya
SThaya
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Conti...


In your Where condition  please call the sample below

physicianname in(select [param] from fn_MVParam (@physicianName,',')
Avatar of Jim Horn
^^^ very nice ^^^
Avatar of parpaa

ASKER

That would be great idea using function, unforteuntely I am using dyn sql.
I cant create function on every DB :(
no need for such a huge function...go for this..might helps you

declare @dd as varchar(max)
set @dd='test,test1,test2,test3'
set @dd=''''+REPLACE(@dd,',',''',''')+''''
select @dd
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
"Thanks DCPKing for quick response. How would I get this?"

You're assembling your list of doctors somewhere in some application: instead of putting commas alone between the names you need to have some ticks too.
Using @physicianName varchar(200) = 'ABBAS, SHAHIDA, ABBASI, ARSHIA',
with your code looking like this:
('''+@PhysicianName+''')
you get this:
where [databaseName].dbo.physicians.Name in ('ABBAS, SHAHIDA, ABBASI, ARSHIA')
instead of
where [databaseName].dbo.physicians.Name in ('ABBAS, SHAHIDA', 'ABBASI, ARSHIA').

You need to get this as a piece of code setting the Physician's name list as follows:
where [databaseName].dbo.physicians.Name in ('ABBAS, SHAHIDA'', ''ABBASI, ARSHIA')

How you achieve this depends on what language you're using to create the string ...

hth

Mike

PS:

I'm assuming that your names are
ABBAS, SHAHIDA
ABBASI, ARSHIA
etc.

In SSRS you should be able to define the parameter in two parts - the Label and the Value - and your user selects (in multiselect) by choosing Labels, although what gets passed back are Values. So you can display ABBAS, SHAHIDA and pass back ''ABBAS, SHAHIDA or whatever ends up working in your code.
You can create multiple selection of Parameter and parameter content like 'para1,para2 etc.'

Hence you have to change you datasource query or stored procedure to work.

you have to use in