Array of Value

I'm learning T-SQL, and I want to know what are the various ways to represent an array of values. For example, I might like to have an array of record IDs, which I feed to an "IN" clause. My guess is that such an "array" would be implemented by using a RecordSet. If yes, thats good - but what are the other ways (if any)? If no, then what other kinds of variables or parameters allow me to do something like that?You can use subquery for that like in:

WHERE SomeColumn IN (SELECT SomeColumn From SomeTable)

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Search the forums for "split function". You can then pass values into a stored procedure with a varchar like "2,34,67,98" and have that string split into a set that can be used in an IN clause. These split functions basically break the string up and puts the CSVs into a temporary table and then (similar to the above suggestion) selects the values from the table.

|||But what about doing a select that has results stored in a (single-column) RecordSet? I've not tried it, but can't that RecordSet be passed around as a parameter, and then fed "as is" into an "IN" clause? Is that not a common/popular thing to do? If not, why not?|||

>>Is that not a common/popular thing to do?<<

Funny way to put this. Common? Yes. Popular? Yes. Natively supported in SQL Server? Nope.

The "classic" paper on the subject is here: http://www.sommarskog.se/arrays-in-sql.html

You can do it with a table variable, but sadly, you cannot pass this around as a parameter.

Array in Store procedure

Dear all,

Sometimes I happened the require that the number of input parameters
of SP is not fixed, Can sql T-SQL handle the array (dynamic array)?
Does anybody ever used an array name as the input parameter to call
the SP?

thanks,

RobertSQL doesn't have arrays but see this article for some alternatives:

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

--
David Portas
SQL Server MVP
--|||Robert Song wrote:

> Dear all,
> Sometimes I happened the require that the number of input parameters
> of SP is not fixed, Can sql T-SQL handle the array (dynamic array)?
> Does anybody ever used an array name as the input parameter to call
> the SP?
> thanks,
> Robert
I had this problem a year ago and solved it using a udf named fn_Split
I found it pretty fast.
You give it a list of parameters as '1,2,3,4,5,6' and it returns a temp
table
so you can select from fn_Split() and have something like
1
2
3
4
5
6

i found a version of this udf at
http://www.umachandar.com/technical...tyFns/Main7.htm

hth

f.|||Hi David,

Thank you so much for your reply, i will read the article later on.

Cheers,

Robert Song

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

are there any MSDE T-SQL code limitations?

I am writing a database app which will run under MSDE on the user's systems.

Other than limitations as to the number of concurrent users, are there any
issues that I need to be aware of when programming the database using SQL
Server 2000? i.e., are there T-SQL programming statements that will run
under SQL Server 2000 as part of my development enviornemnt that won't run
under MSDE on the end users machines, or will MSDE handle anything that SQL
Server can from the database engine standpoint?

Thanks!hi Mike,
"Mike N." <BadDog@.thepound.com> ha scritto nel messaggio
news:r3erb.5780$942.4765@.newssvr25.news.prodigy.co m...
> I am writing a database app which will run under MSDE on the user's
systems.
> Other than limitations as to the number of concurrent users, are there any
> issues that I need to be aware of when programming the database using SQL
> Server 2000? i.e., are there T-SQL programming statements that will run
> under SQL Server 2000 as part of my development enviornemnt that won't run
> under MSDE on the end users machines, or will MSDE handle anything that
SQL
> Server can from the database engine standpoint?

all T-SQL statements SQL Server 2000 supports are supported by MSDE...
you only (only...) have database limitation size, limited replication
support, no mail support...
for further info please visit
http://www.microsoft.com/sql/msde/p...fo/features.asp

hth
Andrea Montanari (Microsoft MVP - SQL Server)
andrea.sql@.virgilio.it
http://www.asql.biz/DbaMgr.shtm http://italy.mvps.org
DbaMgr2k ver 0.4.0 - DbaMgr ver 0.50.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
--- remove DMO to reply