passing an "array" sturcture to a SP to use on a

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 2000 Forums

SQL Server Development (2000)

passing an "array" sturcture to a SP to use on a

Author

Topic

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2006-02-08 : 14:36:58

i want to pass this structure
5612,8451,65456
to a Stored procedure but not as a string
beacuse if i use it as a string
when i use it with a query that i do :
select..... where id in('5612,8451,65456') -->.will result with nothing

i muest have :
select..... where id in(5612,8451,65456)
what can i do>?
thanks in advance
peleg

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-02-08 : 14:52:59

http://www.sqlteam.com/item.asp?ItemID=11499

Tara Kizer
aka tduggan

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2006-02-08 : 15:22:45

i also found this the same
[url]http://www.codeproject.com/database/Pass_Array_To_SP.asp[/url]

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

biggs
Starting Member

10 Posts

Posted - 2006-02-08 : 21:58:36

I'm no expert here, and someone please tell me if I'm wrong, but "select ... where id in('5612,8451,65456')" is fine if you pull those single quotes out. That's assuming your id field is numeric. If it's a string, wrap each value in quotes. I think using an SP to convert it to a temp table is overkill.

Now if you're going to have LOTS of ids in your list, then I'd consider a temp table. Or if you want your output to have the failed matches returned (in the case where no match was found for a given id.)

I had to do the exact same thing the other day except mine was with strings. And it worked great. I pass my SP a string variable with all the IN fields joined with commas. The only problem is you need to be careful with how the quotes fit in.

Tony

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-02-08 : 22:37:07

If you have a fairly short array of integers, less than 250 items, this short piece of code will load it into a temp table with no loops or function calls.


declare @array varchar(8000)
declare @sql varchar(8000)
select @array = '100,14,5,66,300,500'
create table #t( num int not null primary key clustered )
select @sql =
	'insert into #t select '+
	replace(@array,',',' union all select ') 
exec (@sql )

select * from #t
drop table #t

Results:


(6 row(s) affected)

num         
----------- 
5
14
66
100
300
500

(6 row(s) affected)

CODO ERGO SUM

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-02-09 : 01:52:27

Also refer where in @MYCSV in this topic
http://sqlteam.com/forums/topic.asp?TOPIC_ID=55210

Madhivanan

Failing to plan is Planning to fail

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2006-02-13 : 02:03:12

nice and shorter way for shure Michael Valentine Jones
:)

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources