SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Delcare variable for Multiple values paramter in SP ???


Delcare variable for Multiple values paramter in SP ???

Author
Message
jbalbo
jbalbo
SSChasing Mays
SSChasing Mays (631 reputation)SSChasing Mays (631 reputation)SSChasing Mays (631 reputation)SSChasing Mays (631 reputation)SSChasing Mays (631 reputation)SSChasing Mays (631 reputation)SSChasing Mays (631 reputation)SSChasing Mays (631 reputation)

Group: General Forum Members
Points: 631 Visits: 668
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)
PB_BI
PB_BI
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2019 Visits: 2519
What specific error do you get?

Forget that - have a look here

:-)




I'm on LinkedIn
Michael_Garrison
Michael_Garrison
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1338 Visits: 2754
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
Luis Cazares
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17126 Visits: 19122
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
Steven.Howes
SSC Eights!
SSC Eights! (890 reputation)SSC Eights! (890 reputation)SSC Eights! (890 reputation)SSC Eights! (890 reputation)SSC Eights! (890 reputation)SSC Eights! (890 reputation)SSC Eights! (890 reputation)SSC Eights! (890 reputation)

Group: General Forum Members
Points: 890 Visits: 2346
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.
wolfkillj
wolfkillj
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1498 Visits: 2582
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
Blog: SQLSouth
Twitter: @SQLSouth
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search