how can i pass multiple values into one variable

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)

how can i pass multiple values into one variable

Author

Topic

salamat_i
Starting Member

1 Post

Posted - 2008-01-17 : 09:56:33

Hello!

Can someone help me with my issue?

For example my SQL statement like below
select * from table1 where emp_id in (1,2,3,4,5)
and I'm trying to create stored procedure where I can pass multiple values into one variable.
create procedure dbo.test
@InsertValue as
select * from table1 where emp_id in (@InsertValue)
but unfortunately it doesn't work.

please advise is it possible to do

thanks,

Salamat

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-01-17 : 10:29:18

http://sommarskog.se/arrays-in-sql.html

also this particular forum is for posting working scripts, not for asking questions.

elsasoft.org

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-01-17 : 10:33:27

moved from script library

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-17 : 11:41:21

also you can use
select * from table1 where @InsertValue like '%'+ CAST(ISNULL(emp_id,0) AS varchar(20)) +'%'

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-01-17 : 11:49:34

or, wait for 2008. in sql server 2008 you can pass a table variable to a proc IIRC.

elsasoft.org

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-01-18 : 02:34:05

quote:
Originally posted by visakh16

also you can use
select * from table1 where @InsertValue like '%'+ CAST(ISNULL(emp_id,0) AS varchar(20)) +'%'

That should be

select * from table1 where ','+@InsertValue+',' like '%,'+ CAST(ISNULL(emp_id,0) AS varchar(20)) +',%'

Note the commas I appended

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources