Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Delcare variable for Multiple values paramter in SP ??? Expand / Collapse
Author
Message
Posted Thursday, December 5, 2013 7:58 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, November 24, 2014 2:55 PM
Points: 301, Visits: 500
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)
Post #1520101
Posted Thursday, December 5, 2013 8:50 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Today @ 6:28 AM
Points: 608, Visits: 1,165
What specific error do you get?

Forget that - have a look here






I'm on LinkedIn
Post #1520150
Posted Thursday, December 5, 2013 1:13 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 11:09 AM
Points: 1,141, Visits: 2,489
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.
Post #1520317
Posted Thursday, December 5, 2013 3:16 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 10:03 AM
Points: 3,943, Visits: 8,942
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.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1520349
Posted Friday, December 6, 2013 7:40 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Tuesday, November 25, 2014 7:27 AM
Points: 610, Visits: 2,139
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.
Post #1520586
Posted Friday, December 6, 2013 12:00 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, October 28, 2014 12:50 PM
Points: 1,061, Visits: 2,580
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
Post #1520701
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse