Array (or similar thing ) in sql server

I would like to write a fun or stored procedure to
do some operation.
It require me to know that what category is currently belong to certain people
(people_table: category_table
1 to Many)
However, when i use the select statement in stored proc, it return a set of result, not a scalar , therefore, i cannot use the variable to hold it. In addition, there are no array in SQL server.
Question:
1. Is there any way to hold the collection of result(like array)?
2. Also, how to determine to use fun or stored procedure?
(Since a integer is need to return by them)
Thx

When you perform a select statement you're going to get back records in a tabular format. The easiest way to do what you want is to use the select statement and in your code load that into a datatable and if you'd like put that into an array. I guess you could use an ExecuteReader function and then load it into your array... but that sound like a lot of code. If you just need to return one value then use the ExecuteScaler and assign that to your variable. Hope this helps.|||Thx!
However, I want to do this job inside the db, but not using executereader
First, the performance is better as it is operated in db, not need to take data outside
Second, the arhcit design require me to encapsulate the some logic in DB, I am sure there are some way to deal with this problem.
I have think to create a temp table or similar method. However, I am new to db programe. Can anyone give me some idea?
|||Try this link do Arrays in SQL Server. Hope this helps.
http://www.sommarskog.se/arrays-in-sql.html|||Great. Thx