Delcare variable for Multiple values paramter in SP ???

Question

Post reply

Delcare variable for Multiple values paramter in SP ???

jbalbo

SSCertifiable

Points: 7583
More actions
December 5, 2013 at 7:58 am

#395947

Hi
i get an error when I show multiple values in the parameter
Do I declare the variable differently in the SP?
...@Code varchar(10)
,@Days varchar(10)
,@Begin VARCHAR(10)
, @End varchar(10)
, @Zip varchar(1000) Zip is the only parameter to accept multiple values..
the where clause is like....
where.....
AND dbo.Address.Zip in (@Zip)

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

PB_BI SSCoach Points: 17463 More actions · Answer 1

~~What specific error do you get?~~

Forget that - have a look here[/url]

🙂

I'm on LinkedIn

Michael_Garrison Hall of Fame Points: 3110 More actions · Answer 2

Yes, read this article. I had to do learn same thing regarding multi valued params and stored procs. Using the JOIN and then in the stored proc code, use a split string function when values are passed in. something like:

ld.ORIGINCITY IN (select * from [dbo].[fn_SplitStringList](@OriginCity)) Can search for a splitstring function in google, there are plenty of them.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

Michael_Garrison (12/5/2013)
Yes, read this article. I had to do learn same thing regarding multi valued params and stored procs. Using the JOIN and then in the stored proc code, use a split string function when values are passed in. something like:
ld.ORIGINCITY IN (select * from [dbo].[fn_SplitStringList](@OriginCity)) Can search for a splitstring function in google, there are plenty of them.

And you can find the best (no-CLR) splitting function in here: http://www.sqlservercentral.com/articles/Tally+Table/72993/

Read the article and the comments for the best performance and test it yourself.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Steven.Howes SSCertifiable Points: 6230 More actions · Answer 4

What's the error?

When you pass a multivalue parameter you have to do a

=join(Parameters!PraramName.Value,",")

I always forget this step, that and forgetting to make my receiving SP variable large enough to hold all the values.

Jason Wolfkill SSCrazy Eights Points: 9832 More actions · Answer 5

Steven.Howes (12/6/2013)
What's the error?
When you pass a multivalue parameter you have to do a
=join(Parameters!PraramName.Value,",")
I always forget this step, that and forgetting to make my receiving SP variable large enough to hold all the values.

Wasn't this a new feature with SSRS 2008? IIRC, there was no other way to do this than splitting the string in the stored procedure with SSRS 2005, so if the OP is still using 2005, he'll have to go that route.

Jason Wolfkill