KnowledgeHub
Questions
Tags
Users
Search
Alex Rivera
|
Logout
Edit Question
Title
Body
I have a table which references itself and I need to be able to select the parent and all it's child records from a given parent Id. My table is as follows: ID | ParentID | Name ----------------------- 1 NULL A 2 1 B-1 3 1 B-2 4 2 C-1 5 2 C-2 So for the above example I'd like to be able to pass in a value of 1 and get all the records above. So far, I've come up with the following recursive table-valued-function but it's not behaving as expected (only returning the first record). CREATE FUNCTION [dbo].[SelectBranches] ( @id INT ,@parentId INT ) RETURNS @branchTable TABLE ( ID INT ,ParentID INT ,Name INT ) AS BEGIN IF @branchId IS NOT NULL BEGIN INSERT INTO @branchTable SELECT ID ,ParentID ,Name FROM tblLinkAdvertiserCity WHERE ID = @id END INSERT INTO @branchTable SELECT br.ID ,br.ParentID ,br.Name FROM @branchTable b CROSS APPLY dbo.SelectBranches(NULL, b.ParentID) br RETURN END GO
Tags (comma-separated)
Save Edits
Cancel