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********@nos pam.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****@sommarsk og.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*******@eart hlink.net> wrote in message
news:11******** *************@g 49g2000cwa.goog legroups.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********@nos pam.nospam> wrote in message
news:e0******** ******@TK2MSFTN GP12.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*******@eart hlink.net> wrote in message
news:11******** *************@g 49g2000cwa.goog legroups.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********@nos pam.nospam>
| References: <e0************ **@TK2MSFTNGP12 .phx.gbl>
<11************ *********@g49g2 000cwa.googlegr oups.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.publi c.dotnet.framew ork.aspnet,micr osoft.public.sq lserver.program m
ing
| NNTP-Posting-Host: c-24-63-42-200.hsd1.ma.com cast.net 24.63.42.200
| Path: TK2MSFTNGXA01.p hx.gbl!TK2MSFTN GP08.phx.gbl!tk 2msftngp13.phx. gbl
| Xref: TK2MSFTNGXA01.p hx.gbl microsoft.publi c.sqlserver.pro gramming:128510
microsoft.publi c.dotnet.framew ork.aspnet:1353 92
| X-Tomcat-NG: microsoft.publi c.dotnet.framew ork.aspnet
|
| Thanks for your help.
|
| :-|
|
| --
| David Lozzi
| Web Applications Developer
| dlozzi@(remove-this)delphi-ts.com
|
|
|
| "--CELKO--" <jc*******@eart hlink.net> wrote in message
| news:11******** *************@g 49g2000cwa.goog legroups.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***@DIESPAMM ERSDIEtakempis. com> wrote in message
news:%2******** ********@TK2MSF TNGP09.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*******@eart hlink.net> wrote in message
news:11******** *************@g 49g2000cwa.goog legroups.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********@gma il.com>
| References: <e0************ **@TK2MSFTNGP12 .phx.gbl>
<11************ *********@g49g2 000cwa.googlegr oups.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.publi c.dotnet.framew ork.aspnet,micr osoft.public.sq lserver.program m
ing
| NNTP-Posting-Host: 180.239.88.202. asianet.co.in 202.88.239.180
| Path: TK2MSFTNGXA01.p hx.gbl!TK2MSFTN GP08.phx.gbl!tk 2msftngp13.phx. gbl
| Xref: TK2MSFTNGXA01.p hx.gbl microsoft.publi c.sqlserver.pro gramming:128538
microsoft.publi c.dotnet.framew ork.aspnet:1354 33
| X-Tomcat-NG: microsoft.publi c.dotnet.framew ork.aspnet
|
| >I believe SQL Server 2005 supports arrays
| Really?
|
| --
| Roji. P. Thomas
| Net Asset Management
| http://toponewithties.blogspot.com
|
|
| "Kevin Spencer" <ke***@DIESPAMM ERSDIEtakempis. com> wrote in message
| news:%2******** ********@TK2MSF TNGP09.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*******@eart hlink.net> wrote in message
| > news:11******** *************@g 49g2000cwa.goog legroups.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.
| >>
| >
| >
|
|
|