Click here to monitor SSC
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
SSC-Addicted
SSC-Addicted (445 reputation)SSC-Addicted (445 reputation)SSC-Addicted (445 reputation)SSC-Addicted (445 reputation)SSC-Addicted (445 reputation)SSC-Addicted (445 reputation)SSC-Addicted (445 reputation)SSC-Addicted (445 reputation)

Group: General Forum Members
Points: 445 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
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: 1517 Visits: 2450
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: 1262 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
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10552 Visits: 18519
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
Right there with Babe
Right there with Babe (716 reputation)Right there with Babe (716 reputation)Right there with Babe (716 reputation)Right there with Babe (716 reputation)Right there with Babe (716 reputation)Right there with Babe (716 reputation)Right there with Babe (716 reputation)Right there with Babe (716 reputation)

Group: General Forum Members
Points: 716 Visits: 2332
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
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

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