469,276 Members | 1,674 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,276 developers. It's quick & easy.

SQL server stored procedure question

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
Jul 20 '05 #1
4 8647
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 )
Jul 20 '05 #2
[posted and mailed, please reply in public]

mingzhen (mi*********@yahoo.com) writes:
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.


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, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #3
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 <so****@algonet.se> wrote in message news:<Xn********************@127.0.0.1>...
[posted and mailed, please reply in public]

mingzhen (mi*********@yahoo.com) writes:
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.


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.

Jul 20 '05 #4
mingzhen (mi*********@yahoo.com) writes:
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.


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, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Niyazi | last post: by
5 posts views Thread by Alan T | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.