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?

@abatishchev omg, you edited his question. kthxbye

alex
– alex

2010-10-22 12:29:49 +00:00
Commented Oct 22, 2010 at 12:29 — alex
– alex, Commented Oct 22, 2010 at 12:29
@alex: Do you think i shouldn't?

abatishchev
– abatishchev

2010-10-22 12:33:11 +00:00
Commented Oct 22, 2010 at 12:33 — abatishchev
– abatishchev, Commented Oct 22, 2010 at 12:33

Martin Smith · Accepted Answer · 2010-10-22 13:09:54Z

3

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

edited Oct 22, 2010 at 13:09

answered Oct 22, 2010 at 12:34

Martin Smith

457k97 gold badges777 silver badges887 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

0dd_b1t Over a year ago

you have a little mistake - "JOIN divisions d ON d.id = y.sub_id" change this and i will mark your answer as accepted =)

Martin Smith Over a year ago

@0dd_b1t - Ah I often mess that up when I haven't got the data to test against! Done.

abatishchev · Accepted Answer · 2010-10-22 12:32:04Z

1

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

answered Oct 22, 2010 at 12:32

abatishchev

101k88 gold badges304 silver badges443 bronze badges

1 Comment

abatishchev Over a year ago

@0dd_b1t: Seems that @Martin's answer is what you're looking for

Collectives™ on Stack Overflow

How to create SQL Server stored procedure returning an array?

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related