Array in TSQL?

David Lozzi · Nov 1, 2005

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,

Beano · Nov 1, 2005

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 · Nov 1, 2005

David said:
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.

--CELKO-- · Nov 1, 2005

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 · Nov 1, 2005

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.

Bruce Barker · Nov 2, 2005

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 · Nov 2, 2005

Thanks for your help.

:-|

Steven Cheng[MSFT] · Nov 2, 2005

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" <[email protected]>
| References: <[email protected]>
<[email protected]>
| 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: <uen5c#[email protected]>
| 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!tk2msftngp13.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
|
|
|
| | > 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 · Nov 2, 2005

I believe SQL Server 2005 supports arrays
Really?

Steven Cheng[MSFT] · Nov 2, 2005

Erland Sommarskog · Nov 2, 2005

Kevin said:
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.

Guest · Nov 2, 2005

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>'

Guest · Nov 2, 2005

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

Kevin Spencer · Nov 2, 2005

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.

Hugo Kornelis · Nov 2, 2005

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

Erland Sommarskog · Nov 2, 2005

Kevin said:
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.

Rebecca York · Nov 4, 2005

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 ) = ','

SqlDataSource TSQL vs Procs	8	Mar 16, 2007
Can CheckBoxList items be used in SelectParameters of ODS	1	Sep 25, 2008
Execute Stored Procedure	6	Oct 22, 2006
Output Parameter?	1	Aug 25, 2006
Convert VB.NET to TSQL PROC & Reference a Proc from another Proc	3	May 26, 2005
Please help.....	2	Dec 11, 2007
Create User Wizard Insert Issue	1	May 17, 2010
Populate TextBox With DB Records	2	Oct 6, 2006

Array in TSQL?

David Lozzi

Beano

Erland Sommarskog

--CELKO--

Kevin Spencer

Bruce Barker

David Lozzi

Steven Cheng[MSFT]

Roji. P. Thomas

Steven Cheng[MSFT]

Erland Sommarskog

Guest

Guest

Kevin Spencer

Hugo Kornelis

Erland Sommarskog

Rebecca York

Ask a Question

Similar Threads