[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 534
  • Last Modified:

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
0
parpaa
Asked:
parpaa
  • 2
  • 2
  • 2
  • +4
2 Solutions
 
DcpKingCommented:
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'
0
 
parpaaAuthor Commented:
Thanks DCPKing for quick response. How would I get this?
0
 
SThayaCommented:
Hi,


 use the below function for mutlivalue parametrs and call the same in the Stored procedure .. this will help you to run the SP with multi parameters


create FUNCTION [dbo].[fn_MVParam](@RepParam varchar(max), @Delim char(1)= ',')
RETURNS @VALUES TABLE ([Param] varchar(max))AS
   BEGIN
   DECLARE @chrind INT
   DECLARE @Piece varchar(max)
   SELECT @chrind = 1
   WHILE @chrind > 0
      BEGIN
         SELECT @chrind = CHARINDEX(@Delim,@RepParam)
         IF @chrind > 0
            SELECT @Piece = LEFT(@RepParam,@chrind - 1)
         ELSE
            SELECT @Piece = @RepParam
         INSERT @VALUES([Param]) VALUES(@Piece)
         SELECT @RepParam = RIGHT(@RepParam,LEN(@RepParam) - @chrind)
         IF LEN(@RepParam) = 0 BREAK
      END
   RETURN
END
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
SThayaCommented:
Conti...


In your Where condition  please call the sample below

physicianname in(select [param] from fn_MVParam (@physicianName,',')
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
^^^ very nice ^^^
0
 
parpaaAuthor Commented:
That would be great idea using function, unforteuntely I am using dyn sql.
I cant create function on every DB :(
0
 
keyuCommented:
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
0
 
ValentinoVBI ConsultantCommented:
I've covered this "issue" in an article, have a look here: http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/MS-SQL_Reporting/A_2002-Reporting-On-Data-From-Stored-Procedures-part-2.html

It should give you a good understanding of what's going on, and how to get it working.
0
 
DcpKingCommented:
"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.
0
 
Alpesh PatelAssistant ConsultantCommented:
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
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 2
  • 2
  • 2
  • +4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now