SQL server stored procedure question

[mingzhen]

Hi,

I have a problem here when I code stored procedure to manipulate data
in database. My idea is that if I can save all data I get in some
arrays I should be able to manipulate the arrays directly. But I do
not know how to declare arrays in SQL server stored procedure and do
not know other ways to do the same thing. Please help me out. Thanks
in advance.

-mingzhen

[Anith Sen]

Re: SQL server stored procedure question

There are no 'arrays' or array datatypes in SQL Server. Generally developers
come up with many work arounds using strings, XML etc. You can find an
exhaustive article at :

http://www.algonet.se/~sommar/arrays-in-sql.html

--
- Anith
( Please reply to newsgroups only )

[Erland Sommarskog]

Re: SQL server stored procedure question

[posted and mailed, please reply in public]

mingzhen (mingzhen401@ya hoo.com) writes:[color=blue]
> I have a problem here when I code stored procedure to manipulate data
> in database. My idea is that if I can save all data I get in some
> arrays I should be able to manipulate the arrays directly. But I do
> not know how to declare arrays in SQL server stored procedure and do
> not know other ways to do the same thing. Please help me out. Thanks
> in advance.[/color]

It sounds like you are used to programming in 3GL, and want to apply
the same methods there. Actually, there are arrays in SQL, just
there right before your eyes. But they are not called arrays, but
tables, and are in many extents much more powerful than arrays. Arrays
you typically handle sequentially. While you can do this with a table,
you can also apply operations on many rows at the same time, which is
usually several magnitudes faster than sequential processeing.

--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

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

[mingzhen]

Re: SQL server stored procedure question

Yes, I know that sometimes table can be more flexible than arrays. But
my main problem is that I want to abstract data from each record in
the table, and concatenate these dat a into one string. Then I can
compare these strings to chech if there are some duplicate records in
the specific table.

So, my problems are:
1. identify primary key field in a specific table
2. concatenate data from fields except primary key field in one record
into one string, and different strings for different records in the
table

HOw can I do this using table or other methods? Thanks for help.

--mingzhen


Erland Sommarskog <sommar@algonet .se> wrote in message news:<Xns93D1B9 9A07EYazorman@1 27.0.0.1>...[color=blue]
> [posted and mailed, please reply in public]
>
> mingzhen (mingzhen401@ya hoo.com) writes:[color=green]
> > I have a problem here when I code stored procedure to manipulate data
> > in database. My idea is that if I can save all data I get in some
> > arrays I should be able to manipulate the arrays directly. But I do
> > not know how to declare arrays in SQL server stored procedure and do
> > not know other ways to do the same thing. Please help me out. Thanks
> > in advance.[/color]
>
> It sounds like you are used to programming in 3GL, and want to apply
> the same methods there. Actually, there are arrays in SQL, just
> there right before your eyes. But they are not called arrays, but
> tables, and are in many extents much more powerful than arrays. Arrays
> you typically handle sequentially. While you can do this with a table,
> you can also apply operations on many rows at the same time, which is
> usually several magnitudes faster than sequential processeing.[/color]

[Erland Sommarskog]

Re: SQL server stored procedure question

mingzhen (mingzhen401@ya hoo.com) writes:[color=blue]
> Yes, I know that sometimes table can be more flexible than arrays. But
> my main problem is that I want to abstract data from each record in
> the table, and concatenate these dat a into one string. Then I can
> compare these strings to chech if there are some duplicate records in
> the specific table.
>
> So, my problems are:
> 1. identify primary key field in a specific table
> 2. concatenate data from fields except primary key field in one record
> into one string, and different strings for different records in the
> table
>
> HOw can I do this using table or other methods? Thanks for help.[/color]

I'm really sure that I follow, so I like to make the following standard
suggestion. Post a script that contains CREATE TABLE statement(s) for
the involved table(s) (preferably simplified) and INSERT statements
with sample data. With the script, include the desired result.

--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

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