Array in TSQL?

[Erland Sommarskog]

Re: Array in TSQL?

Kevin Spencer (kevin@DIESPAMM ERSDIEtakempis. com) writes:[color=blue]
> In fact, while arrays are not a native SQL data type, XML is in fact, a
> Transact-SQL data type. So, one could certainly use an XML array, or any
> other type of XML structure in a Stored Procedure.[/color]

Yes, XML is indeed a native data type, and there is a whole of things you
can use that data type for. Far more things that you can think of at
first hand. If all you want is a simple array, XML is an overkill in my
opinion. But if you have structured data that you want to pass to SQL
Server, putting it into an XMl document and shred it in SQL Server, can
reduced load time immensly by savin network roundtrips. In fact, this
you can do already in SQL 2000. But you can do a lot more with XML in
SQL 2005.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Rebecca York]

Re: Array in TSQL?

USE Common

DECLARE @CSL VARCHAR(8000)

SELECT @CSL = '6,4,8,19,2'

SELECT
SUBSTRING( CSL , n , CHARINDEX( ',' , CSL , n ) - n ) AS [items]
FROM
(
SELECT @CSL + ',' AS CSL
) DataList
INNER JOIN
tblNumbers /* contains n = 1 to 1,000,000 */
ON
tblNumbers.N BETWEEN 1 AND DATALENGTH( @CSL )
WHERE
SUBSTRING( ',' + CSL , N , 1 ) = ','


"David Lozzi" <DavidLozzi@nos pam.nospam> wrote in message
news:e03WQ9y3FH A.3600@TK2MSFTN GP12.phx.gbl...[color=blue]
> Hello,
>
> I have some code that adds a new user. The new user has a checkboxlist of
> items which they can be associated with. I would like to send this list of
> items to TSQL along with the new user information. I would guess to[/color]
combine[color=blue]
> the selected items like so: "6,4,8,19,2 ".
>
> Kind of do the following:
>
> INSERT into tblUser (fields) VALUES (data)
> Declare @userID as integer
> SET @UserID = @@IDENTITY
>
> for each item in @Selected
> INSERT into tblSelections (field) VALUE (item, @UserID)
>
> I know above isn't exactly possible, but can something similiar be done? I
> dont want to have to run a proc for each item from my asp.net pages....[/color]