How to create SQL Server stored procedure returning an array?

Sorry for this maybe f开发者_如何转开发oolish question but i'm newbie to SQL Server. Here structure of my table of divisions at organization:

id int(16) -- simply unique id
sub_id int(16) -- this id of parent division in this table (if zero, than it don't have parent)
name VARCHAR(200) -- name of division

I need to create a simply procedure which return me all id of subdivisions in some division (with top division id too). I think i need array with subid's in loop, but i don't know how to create array in SQL Serve o_0 (omg.. array exist in (T)SQL language ? =). Help please. Or maybe another way to get it?

If I have understood your question correctly you need a recursive CTE.

CREATE PROCEDURE dbo.foo
@id int
AS

WITH divisions AS
(
SELECT id, sub_id, name
FROM YourTable
WHERE id = @id
UNION ALL
SELECT y.id, y.sub_id, y.name
FROM YourTable y
JOIN divisions d ON d.id = y.sub_id
)

SELECT id, sub_id, name
FROM divisions

SELECT id
FROM table
WHERE sub_id = @target
UNION ALL
SELECT @target