SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 how can i pass multiple values into one variable
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

salamat_i
Starting Member

Kazakhstan
1 Posts

Posted - 01/17/2008 :  09:56:33  Show Profile  Click to see salamat_i's MSN Messenger address  Reply with Quote
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

Edited by - spirit1 on 01/17/2008 10:33:18

jezemine
Flowing Fount of Yak Knowledge

USA
2886 Posts

Posted - 01/17/2008 :  10:29:18  Show Profile  Visit jezemine's Homepage  Reply with Quote
http://sommarskog.se/arrays-in-sql.html

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


elsasoft.org
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11751 Posts

Posted - 01/17/2008 :  10:33:27  Show Profile  Visit spirit1's Homepage  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 01/17/2008 :  11:41:21  Show Profile  Reply with Quote
also you can use
select * from table1 where @InsertValue like '%'+ CAST(ISNULL(emp_id,0) AS varchar(20)) +'%'
Go to Top of Page

jezemine
Flowing Fount of Yak Knowledge

USA
2886 Posts

Posted - 01/17/2008 :  11:49:34  Show Profile  Visit jezemine's Homepage  Reply with Quote
or, wait for 2008. in sql server 2008 you can pass a table variable to a proc IIRC.


elsasoft.org
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22769 Posts

Posted - 01/18/2008 :  02:34:05  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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

Edited by - madhivanan on 01/18/2008 02:35:14
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.19 seconds. Powered By: Snitz Forums 2000