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
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1545 Visits: 688
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
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4560 Visits: 2745
What specific error do you get?

Forget that - have a look here

:-)




I'm on LinkedIn
Michael_Garrison
Michael_Garrison
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1624 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
SSC-Forever
SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)

Group: General Forum Members
Points: 43569 Visits: 19863
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
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1690 Visits: 2354
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
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2802 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