Array in TSQL?

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.comIt 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|||David Lozzi (DavidLozzi@.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, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||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.|||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--" <jcelko212@.earthlink.net> wrote in message
news:1130887553.702237.72860@.g49g2000cwa.googlegroups.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.
>|||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" <DavidLozzi@.nospam.nospam> wrote in message
news:e03WQ9y3FHA.3600@.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
>
>|||Thanks for your help.
:-|
David Lozzi
Web Applications Developer
dlozzi@.(remove-this)delphi-ts.com
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1130887553.702237.72860@.g49g2000cwa.googlegroups.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.
>|||>I believe SQL Server 2005 supports arrays
Really?
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Kevin Spencer" <kevin@.DIESPAMMERSDIEtakempis.com> wrote in message
news:%23NDUr8z3FHA.3296@.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--" <jcelko212@.earthlink.net> wrote in message
> news:1130887553.702237.72860@.g49g2000cwa.googlegroups.com...
>|||Kevin Spencer (kevin@.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, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||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 combin
e
> 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
>
>
>