Array in TSQL?

[David Lozzi]

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 combine
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....

Thanks,

--
David Lozzi
Web Applications Developer
dlozzi@(remove-this)delphi-ts.com

[Beano]

It would definetely be easiest to call a proc each time. Othewise you
could pass in the comma seperated string into the proc, and then do a
while loop with that string.

While CharIndex(",",@Selected) != 0
Begin
-- Get Value before first comma code

-- Insert value code

-- Delete first value and comma code
End

[Erland Sommarskog]

David Lozzi (Da********@nospam.nospam) writes:

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 combine 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....

See http://www.sommarskog.se/arrays-in-sql.html#iterative for some
solutions.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[--CELKO--]

You have missed the foundations of RDBMS and really need to get a book
or a class before you try to code anything.

You want to violate First Normal Form (1NF). All data values are
scalar; there are no arrays. Each of those attribures would be a
separate column.

Rows are not records; fields are not columns; tables are not files.

We do not put silly redundant prefixes like "tbl-" on table names.
Look up ISO-11179.

I hope you know that IDENTITY cannot be a relational key by definition.
But you are using a bad thing in the wrong way. It mimics a
sequential file record number counter without your intervention when
you declare it as part of the DDL.

Do you know about check digits, a Regular Expression or some other rule
to validate your user id?

SQL is a set-oriented language, so you can insert a query result into a
base table or updatable VIEW. You are writing SQL like it was a 3GL.
You need a lot more help thanyou can get in a Newsgroup.

[Kevin Spencer]

I believe SQL Server 2005 supports arrays. I'm just getting into it, though.

--
HTH,

Kevin Spencer
Microsoft MVP
..Net Developer
A watched clock never boils.

"--CELKO--" <jc*******@earthlink.net> wrote in message
news:11*********************@g49g2000cwa.googlegro ups.com...

You have missed the foundations of RDBMS and really need to get a book
or a class before you try to code anything.

You want to violate First Normal Form (1NF). All data values are
scalar; there are no arrays. Each of those attribures would be a
separate column.

Rows are not records; fields are not columns; tables are not files.

We do not put silly redundant prefixes like "tbl-" on table names.
Look up ISO-11179.

I hope you know that IDENTITY cannot be a relational key by definition.
But you are using a bad thing in the wrong way. It mimics a
sequential file record number counter without your intervention when
you declare it as part of the DDL.

Do you know about check digits, a Regular Expression or some other rule
to validate your user id?

SQL is a set-oriented language, so you can insert a query result into a
base table or updatable VIEW. You are writing SQL like it was a 3GL.
You need a lot more help thanyou can get in a Newsgroup.

[Bruce Barker]

trival, wrtite a user function that converts a comma seperated list into a
table (the sql equiv of an array)

create function dbo.parseList (@s varchar(2000))
returns @values table (value varchar(2000))
as begin
declare @v varchar(2000) ,@i int
set @i = patIndex('%,%',@s)
while @i > 0 begin
insert @values values (substring(@s,1,@i-1))
set @s = substring(@s,@i+1,len(@s) - @i)
set @i = patIndex('%,%',@s)
end
insert @values values (@s)
return
end

then call like:

INSERT into tblUser (fields) VALUES (data)
SET @UserID = scope_identity()

INSERT tblSelections (field,userid)
select value, @UserID
from dbo.parseList(@selected)
-- bruce (sqlwork.com)


"David Lozzi" <Da********@nospam.nospam> wrote in message
news:e0**************@TK2MSFTNGP12.phx.gbl...

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
combine 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....

Thanks,

--
David Lozzi
Web Applications Developer
dlozzi@(remove-this)delphi-ts.com

[David Lozzi]

Thanks for your help.

:-|

--
David Lozzi
Web Applications Developer
dlozzi@(remove-this)delphi-ts.com

"--CELKO--" <jc*******@earthlink.net> wrote in message
news:11*********************@g49g2000cwa.googlegro ups.com...

You have missed the foundations of RDBMS and really need to get a book
or a class before you try to code anything.

You want to violate First Normal Form (1NF). All data values are
scalar; there are no arrays. Each of those attribures would be a
separate column.

Rows are not records; fields are not columns; tables are not files.

We do not put silly redundant prefixes like "tbl-" on table names.
Look up ISO-11179.

I hope you know that IDENTITY cannot be a relational key by definition.
But you are using a bad thing in the wrong way. It mimics a
sequential file record number counter without your intervention when
you declare it as part of the DDL.

Do you know about check digits, a Regular Expression or some other rule
to validate your user id?

SQL is a set-oriented language, so you can insert a query result into a
base table or updatable VIEW. You are writing SQL like it was a 3GL.
You need a lot more help thanyou can get in a Newsgroup.

[Steven Cheng[MSFT]]

Hi David,

If you really need to pass a combined string and let the TSQL perfom a loop
command execution (instead of constantly execute sqlcommand in .NET code) ,
I'm afraid we have to manually parse the string in TSQL code. Also, it's
better to encapsulate such code in a stored procedure to improve
performance. I think the article Erland has provided is useful for you.

Thanks,

Steven Cheng
Microsoft Online Support

Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

--------------------
| From: "David Lozzi" <Da********@nospam.nospam>
| References: <e0**************@TK2MSFTNGP12.phx.gbl>
<11*********************@g49g2000cwa.googlegroups. com>
| Subject: Re: Array in TSQL?
| Date: Tue, 1 Nov 2005 20:48:50 -0500
| Lines: 41
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2900.2670
| X-RFC2646: Format=Flowed; Original
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2670
| Message-ID: <ue*************@tk2msftngp13.phx.gbl>
| Newsgroups:
microsoft.public.dotnet.framework.aspnet,microsoft .public.sqlserver.programm
ing
| NNTP-Posting-Host: c-24-63-42-200.hsd1.ma.comcast.net 24.63.42.200
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msft ngp13.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.programming:128510
microsoft.public.dotnet.framework.aspnet:135392
| X-Tomcat-NG: microsoft.public.dotnet.framework.aspnet
|
| Thanks for your help.
|
| :-|
|
| --
| David Lozzi
| Web Applications Developer
| dlozzi@(remove-this)delphi-ts.com
|
|
|
| "--CELKO--" <jc*******@earthlink.net> wrote in message
| news:11*********************@g49g2000cwa.googlegro ups.com...
| > You have missed the foundations of RDBMS and really need to get a book
| > or a class before you try to code anything.
| >
| > You want to violate First Normal Form (1NF). All data values are
| > scalar; there are no arrays. Each of those attribures would be a
| > separate column.
| >
| > Rows are not records; fields are not columns; tables are not files.
| >
| > We do not put silly redundant prefixes like "tbl-" on table names.
| > Look up ISO-11179.
| >
| > I hope you know that IDENTITY cannot be a relational key by definition.
| > But you are using a bad thing in the wrong way. It mimics a
| > sequential file record number counter without your intervention when
| > you declare it as part of the DDL.
| >
| > Do you know about check digits, a Regular Expression or some other rule
| > to validate your user id?
| >
| > SQL is a set-oriented language, so you can insert a query result into a
| > base table or updatable VIEW. You are writing SQL like it was a 3GL.
| >
| >
| > You need a lot more help thanyou can get in a Newsgroup.
| >
|
|
|

[Roji. P. Thomas]

>I believe SQL Server 2005 supports arrays
Really?

--
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Kevin Spencer" <ke***@DIESPAMMERSDIEtakempis.com> wrote in message
news:%2****************@TK2MSFTNGP09.phx.gbl...

I believe SQL Server 2005 supports arrays. I'm just getting into it,
though.

--
HTH,

Kevin Spencer
Microsoft MVP
.Net Developer
A watched clock never boils.

"--CELKO--" <jc*******@earthlink.net> wrote in message
news:11*********************@g49g2000cwa.googlegro ups.com...

You have missed the foundations of RDBMS and really need to get a book
or a class before you try to code anything.

You want to violate First Normal Form (1NF). All data values are
scalar; there are no arrays. Each of those attribures would be a
separate column.

Rows are not records; fields are not columns; tables are not files.

We do not put silly redundant prefixes like "tbl-" on table names.
Look up ISO-11179.

I hope you know that IDENTITY cannot be a relational key by definition.
But you are using a bad thing in the wrong way. It mimics a
sequential file record number counter without your intervention when
you declare it as part of the DDL.

Do you know about check digits, a Regular Expression or some other rule
to validate your user id?

SQL is a set-oriented language, so you can insert a query result into a
base table or updatable VIEW. You are writing SQL like it was a 3GL.
You need a lot more help thanyou can get in a Newsgroup.

[Steven Cheng[MSFT]]

Hi Roji,

I think so. SQL 2005 has integrated the .NET CLR internally. So we can used
net code to create managed store procedure which can utilize most of the
NET basic classes and types.

Thanks,

Steven Cheng
Microsoft Online Support

Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)
--------------------
| From: "Roji. P. Thomas" <th********@gmail.com>
| References: <e0**************@TK2MSFTNGP12.phx.gbl>
<11*********************@g49g2000cwa.googlegroups. com>
<#N**************@TK2MSFTNGP09.phx.gbl>
| Subject: Re: Array in TSQL?
| Date: Wed, 2 Nov 2005 12:34:51 +0530
| Lines: 54
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
| X-RFC2646: Format=Flowed; Response
| Message-ID: <Oy**************@tk2msftngp13.phx.gbl>
| Newsgroups:
microsoft.public.dotnet.framework.aspnet,microsoft .public.sqlserver.programm
ing
| NNTP-Posting-Host: 180.239.88.202.asianet.co.in 202.88.239.180
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msft ngp13.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.programming:128538
microsoft.public.dotnet.framework.aspnet:135433
| X-Tomcat-NG: microsoft.public.dotnet.framework.aspnet
|
| >I believe SQL Server 2005 supports arrays
| Really?
|
| --
| Roji. P. Thomas
| Net Asset Management
| http://toponewithties.blogspot.com
|
|
| "Kevin Spencer" <ke***@DIESPAMMERSDIEtakempis.com> wrote in message
| news:%2****************@TK2MSFTNGP09.phx.gbl...
| >I believe SQL Server 2005 supports arrays. I'm just getting into it,
| >though.
| >
| > --
| > HTH,
| >
| > Kevin Spencer
| > Microsoft MVP
| > .Net Developer
| > A watched clock never boils.
| >
| > "--CELKO--" <jc*******@earthlink.net> wrote in message
| > news:11*********************@g49g2000cwa.googlegro ups.com...
| >> You have missed the foundations of RDBMS and really need to get a book
| >> or a class before you try to code anything.
| >>
| >> You want to violate First Normal Form (1NF). All data values are
| >> scalar; there are no arrays. Each of those attribures would be a
| >> separate column.
| >>
| >> Rows are not records; fields are not columns; tables are not files.
| >>
| >> We do not put silly redundant prefixes like "tbl-" on table names.
| >> Look up ISO-11179.
| >>
| >> I hope you know that IDENTITY cannot be a relational key by definition.
| >> But you are using a bad thing in the wrong way. It mimics a
| >> sequential file record number counter without your intervention when
| >> you declare it as part of the DDL.
| >>
| >> Do you know about check digits, a Regular Expression or some other rule
| >> to validate your user id?
| >>
| >> SQL is a set-oriented language, so you can insert a query result into a
| >> base table or updatable VIEW. You are writing SQL like it was a 3GL.
| >>
| >>
| >> You need a lot more help thanyou can get in a Newsgroup.
| >>
| >
| >
|
|
|

[Erland Sommarskog]

Kevin Spencer (ke***@DIESPAMMERSDIEtakempis.com) writes:

I believe SQL Server 2005 supports arrays. I'm just getting into it,
though.

No, not more than SQL 2000. That is, you can transform a list to table
with a function or similar.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Vadivel]

There are actually two methods to approach this situation. They are:

1. Making use of sp_xml_preparedocument and sp_xml_removedocument and
2. Custom split function (If you want the code snippet for custom split
function .. write back)

Sample table structure:
----------------------------

Create table StudentMaster
(
StudentID int IDENTITY(1,1) NOT NULL,
StudentName varchar(100),
StudentAge int
)

Create table StudentDetails
(
StudentID int,
SubjectName varchar(10)
)

Method 1:
-----------

Create proc InsertStudent
@StudentName varchar(100),
@StudentAge int,
@SubjectString varchar(1000)
AS

Begin Tran StudentTransaction

/* Local Variable Declarations */
Declare @NewRowId int
Declare @SubjectXmlDoc int

/* Insert Master record and get identity value */
Insert Into StudentMaster (StudentName, StudentAge) Values (@StudentName,
@StudentAge)

-- Retreive the last identity value inserted into the Identity column
(StudentID)
Select @NewRowId = SCOPE_IDENTITY()

/* Replace dummy identity value with actual id value */
Select @SubjectString = Replace(@SubjectString, '123456',
Convert(varchar(10), @NewRowId))

/* XML Bulk Insert the Subjects */

-- The below line creates XML document and returns numeric ID
Exec sp_xml_preparedocument @SubjectXmlDoc OUTPUT, @SubjectString

Insert into StudentDetails (StudentId, SubjectName)SELECT StudentId,
SubjectName FROM OPENXML (@SubjectXmlDoc, '/root/row') WITH (StudentId int,
SubjectName varchar(100))

-- Deletes the XML document
Exec sp_xml_removedocument @SubjectXmlDoc
Commit Tran StudentTransaction

/* To test SP */
Exec InsertStudent 'test', 12, '<root><row StudentId="123456"
SubjectName="Maths"/><row StudentId="123456" SubjectName="Science"/></root>'

"David Lozzi" wrote:

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 combine
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....

Thanks,

--
David Lozzi
Web Applications Developer
dlozzi@(remove-this)delphi-ts.com

[Vadivel]

Oops forgot to give the definition in my previous post ...

In order for SQL Server to process XML documents, we can use the system
stored procedure sp_xml_preparedocument, to read the document and verify
whether it is a valid XML document or not. The stored procedure then returns
a numeric handle to the XML document. That handle is passed to and used by
the OPENXML function to convert the tree structure representation of the XML
document to a relational format. The OpenXML() function then executes the
INSERT statement to insert the data in a relational format in a SQL Server
database.

Once a document has been fully processed, we should use the
sp_xml_removedocument stored procedure to reclaim the memory used by the node
tree (by destroying the XML document and release the server’s resources)

Hope this helps!

Best Regards
Vadivel

http://vadivel.blogspot.com
http://thinkingms.com/vadivel

"Vadivel" wrote:

There are actually two methods to approach this situation. They are:

1. Making use of sp_xml_preparedocument and sp_xml_removedocument and
2. Custom split function (If you want the code snippet for custom split
function .. write back)

Sample table structure:
----------------------------

Create table StudentMaster
(
StudentID int IDENTITY(1,1) NOT NULL,
StudentName varchar(100),
StudentAge int
)

Create table StudentDetails
(
StudentID int,
SubjectName varchar(10)
)

Method 1:
-----------

Create proc InsertStudent
@StudentName varchar(100),
@StudentAge int,
@SubjectString varchar(1000)
AS

Begin Tran StudentTransaction

/* Local Variable Declarations */
Declare @NewRowId int
Declare @SubjectXmlDoc int

/* Insert Master record and get identity value */
Insert Into StudentMaster (StudentName, StudentAge) Values (@StudentName,
@StudentAge)

-- Retreive the last identity value inserted into the Identity column
(StudentID)
Select @NewRowId = SCOPE_IDENTITY()

/* Replace dummy identity value with actual id value */
Select @SubjectString = Replace(@SubjectString, '123456',
Convert(varchar(10), @NewRowId))

/* XML Bulk Insert the Subjects */

-- The below line creates XML document and returns numeric ID
Exec sp_xml_preparedocument @SubjectXmlDoc OUTPUT, @SubjectString

Insert into StudentDetails (StudentId, SubjectName)SELECT StudentId,
SubjectName FROM OPENXML (@SubjectXmlDoc, '/root/row') WITH (StudentId int,
SubjectName varchar(100))

-- Deletes the XML document
Exec sp_xml_removedocument @SubjectXmlDoc
Commit Tran StudentTransaction

/* To test SP */
Exec InsertStudent 'test', 12, '<root><row StudentId="123456"
SubjectName="Maths"/><row StudentId="123456" SubjectName="Science"/></root>'

"David Lozzi" wrote:

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 combine
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....

Thanks,

--
David Lozzi
Web Applications Developer
dlozzi@(remove-this)delphi-ts.com

[Kevin Spencer]

Well, Erland, thanks for getting me started in my SQL Server 2005 research!

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.

Would that not be correct?

--
HTH,

Kevin Spencer
Microsoft MVP
..Net Developer
A watched clock never boils.

"Erland Sommarskog" <es****@sommarskog.se> wrote in message
news:Xn**********************@127.0.0.1...

Kevin Spencer (ke***@DIESPAMMERSDIEtakempis.com) writes:

I believe SQL Server 2005 supports arrays. I'm just getting into it,
though.

No, not more than SQL 2000. That is, you can transform a list to table
with a function or similar.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Hugo Kornelis]

On 1 Nov 2005 15:25:53 -0800, --CELKO-- wrote:

You have missed the foundations of RDBMS and really need to get a book
or a class before you try to code anything.

You want to violate First Normal Form (1NF). All data values are
scalar; there are no arrays. Each of those attribures would be a
separate column.

(snip)

Hi Joe,

It would really help if you actually read a post before mindlessly
slapping in one of your boilerplate replies.

If you had read the complete message the OP posted, you'd have known
that he is NOT violating 1NF in his database. The only thing he wants is
to pass a list of values in a call to the database, then have the
database decode it and store the information as seperate rows.

Sheesh!

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[Erland Sommarskog]

Kevin Spencer (ke***@DIESPAMMERSDIEtakempis.com) writes:

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.

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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Rebecca York]

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" <Da********@nospam.nospam> wrote in message
news:e0**************@TK2MSFTNGP12.phx.gbl...

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 combine 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....