How to create SQL Server stored procedure returning an array?

Question

Sorry for this maybe foolish 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?

Answer 1

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

Answer 2

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