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
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
Hope this make sense
Physician-Perform.sql
Physcian-Sample-data.txt
ASKER
Thanks DCPKing for quick response. How would I get this?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Conti...
In your Where condition please call the sample below
physicianname in(select [param] from fn_MVParam (@physicianName,',')
In your Where condition please call the sample below
physicianname in(select [param] from fn_MVParam (@physicianName,',')
^^^ very nice ^^^
ASKER
That would be great idea using function, unforteuntely I am using dyn sql.
I cant create function on every DB :(
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,test 3'
set @dd=''''+REPLACE(@dd,',',' '',''')+'' ''
select @dd
declare @dd as varchar(max)
set @dd='test,test1,test2,test
set @dd=''''+REPLACE(@dd,',','
select @dd
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
"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.physici ans.Name in ('ABBAS, SHAHIDA, ABBASI, ARSHIA')
instead of
where [databaseName].dbo.physici ans.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.physici ans.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'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.physici
instead of
where [databaseName].dbo.physici
You need to get this as a piece of code setting the Physician's name list as follows:
where [databaseName].dbo.physici
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
Hence you have to change you datasource query or stored procedure to work.
you have to use in
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'