SP with multi value parameter - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

SP with multi value parameter

Author

Topic

cipriani1984
Constraint Violating Yak Guru

304 Posts

Posted - 2009-09-04 : 06:02:07

Hi,

When it comes to declaring the values in a SP when I wanna pass multivalues into the parameter is it different?

create proc sp_test @param varchar(max)

select * from table
where field in (@param)

thanks
cipriani

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-04 : 06:03:55

Search for Array+sql server in google

Madhivanan

Failing to plan is Planning to fail

cipriani1984
Constraint Violating Yak Guru

304 Posts

Posted - 2009-09-04 : 06:12:32

yea i did that, but for reporting services its still not returning any values.

cipriani

quote:
Originally posted by madhivanan

Search for Array+sql server in google

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-04 : 06:15:50

quote:
Originally posted by cipriani1984

yea i did that, but for reporting services its still not returning any values.

cipriani

quote:
Originally posted by madhivanan

Search for Array+sql server in google

Madhivanan

Failing to plan is Planning to fail

Are you passing it to the procedure or a report?

Madhivanan

Failing to plan is Planning to fail

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-09-04 : 06:25:12

try like this
u will not get output by using in
declare @param varchar(128)
select @param = '1,2,5'

select * from table where '%,' + @param+ ',%' LIKE '%,' + CAST( field AS VARCHAR(255)) +',%'

exec('select * from table where cast(field as varchar) in('+@param+')')

select * from table where cast(field as varchar) in(@param)

select * from table where patindex('%,' + CAST( field AS VARCHAR(255)) +',%',','+@param+',' )>0

ra.shinde
Posting Yak Master

103 Posts

Posted - 2009-09-04 : 06:26:48

use
select * from table
where ','+@param+',' like '%,' + field + ',%'
if values are separated by other char than comma (,) then make changes appropriately.

Rahul Shinde

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-09-04 : 07:17:52

Look here (googled):
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=108285

No, you're never too old to Yak'n'Roll if you're too young to die.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-09-04 : 12:25:36

Depending on the size of you data (if performance is an issue), you'll probably want to use a string parsing function to parse the array and load it into a temp table or table variable and join to it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources