simulating an array in SQL Server

[csbdeady]

Hi

In a script I'll be using in Query Analyser I want to simulate an array as SQL Server doesn't support them as such.

What I'd like is something like:

@MyArray = "a,b,c,d"

<do_some_processing_of>@MyArray = &quot;a&quot;
<do_some_more_processing to find the 2nd entry>@MyArray = &quot;b&quot;

etc

Hope that makes sense!

Any thoughts?

I can't use a temporary table to hold the array data for various reasons.

Ta
-Colin

 

[mwolf00]

It's unfortunate that you can't use a temp table because that is your answer. An array simultates a table. I think that's why T-SQL doesn't have an array - because you shouldn't need one since you can always create a table (except in your case)... -- Just trying to help...
<--- This is a wolf? We need a new icon.......
mikewolf@tst-us.com

 

[csbdeady]

ok been doing some research

I can't use a temp table as am going to be testing on a database that I'm not allowed to write to (only read).

However I can create a datatype of table in SQL Server 2000 and use that to store my &quot;array&quot;.

I just tried:

DECLARE @SupplierContacts table
(
SupplierID int NOT NULL,
ContactNameTitle nvarchar(62) NULL,
Country nvarchar(15) NULL
)

However I got the message:

Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'table'.

Anyone know why?

Ta!
-Colin

 

[MikeTom]

it works okay.

 

[tlbroadbent]

See the following link for a SQL Server UDF that will parse a character string and return a table. This is an excellent solution when you need to use and array.

Treat Yourself to Fn_Split()
This UDF lets you emulate VB's Split() function and parameter arrays.

http://msdn.microsoft.com/library/en-us/dnsqlmag01/html/TreatYourself.asp

Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions.

 

[mwolf00]

tlbroadbent has some good links for this very question @ thread183-273839 -- Just trying to help...
<--- This is a wolf? We need a new icon.......
mikewolf@tst-us.com