Stored Procedure with Multiple Value

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

Transact-SQL (2000)

Stored Procedure with Multiple Value

Author

Topic

oceanboy
Starting Member

44 Posts

Posted - 2007-09-05 : 04:51:26

Hi all,

Is it possible to write a store procedure that deals with many values? For example

create procedure xxx (@list)
....
select @
from table
where list in @list
...
...

end

Thanks!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-09-05 : 04:53:29

yes. But can you give us more detail what do you want to achieve ?

KH
[spoiler]Time is always against us[/spoiler]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-05 : 04:57:33

See http://www.sommarskog.se/arrays-in-sql-2000.html

E 12°55'05.25"
N 56°04'39.16"

oceanboy
Starting Member

44 Posts

Posted - 2007-09-05 : 05:01:21

khtan,

I would like to write a stored procedure that will process at least 1 item. Normally if the SQL only deal with 1 item, we will write it like this:
select * from table where list = 1

But my plan is to write it so that it will take more than 1 item:
select * from table where list in (1,2,3...)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-09-05 : 05:03:30

see the FAQ http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210 on CSV / Splitting delimited lists & Array

KH
[spoiler]Time is always against us[/spoiler]

oceanboy
Starting Member

44 Posts

Posted - 2007-09-05 : 05:05:56

perhaps I will just use dynamic SQL?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-05 : 05:09:02

select t.*
from table as t
where ',' + @list + ',' like '%,' + t.list + ',%'

E 12°55'05.25"
N 56°04'39.16"

oceanboy
Starting Member

44 Posts

Posted - 2007-09-05 : 05:26:51

what's that Peso? A bit confusing there? Is that an example of the dynamic SQL?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-05 : 05:43:42

No.
It is a different way of treating lists the way you want.

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-05 : 06:20:58

quote:
Originally posted by oceanboy

what's that Peso? A bit confusing there? Is that an example of the dynamic SQL?

You need to read sommarskog's artilce for more details

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-05 : 06:22:07

and this is more on dynamic sql
www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources