I've got a table which has a self-referencing foreign key that signifies its parent row. As one example of the issue in the easiest form we'll make use of this table:
CREATE TABLE Folder( id int IDENTITY(1,1) NOT NULL, --PK parent_id int NULL, --FK folder_name varchar(255) NOT NULL)
I wish to produce a scalar-valued function that will return a concatenated string from the folder's title and all sorts of its parent folder names completely towards the root folder, which may be designated with a null parent_id value.
My current option would be a procedural approach that we assume isn't ideal. Here's what I am doing:
CREATE FUNCTION dbo.GetEntireLineage (@folderId INT) RETURNS VARCHAR(MAX) AS BEGIN DECLARE @lineage VARCHAR(MAX) DECLARE @parentFolderId INT SELECT @lineage = folder_name, @parentFolderId = parent_id FROM Folder WHERE id = @folderId WHILE NOT @parentFolderId IS NULL BEGIN SET @parentFolderId = (SELECT parent_id FROM Folder WHERE parent_id = @parentFolderId) SET @lineage = (SELECT @lineage + '-' + (SELECT folder_name FROM Folder WHERE parent_id = @parentFolderId)) END RETURN @lineage END
It is possible to better method of doing this? I am a skilled programmer but T-SQL not really a familiar world in my experience and that i know these complaints generally need a different approach because of the character of set based data. Any help getting a solution or other tips and methods to cope with T-SQL could be much appreciated.
A hierarchyid is frequently overkill unless of course you've got a really deep hierarchy or large teams of data that may make use of the indexing. This is because fast as possible without altering your schema.
with recursiveCTE (parent_id,concatenated_name) as ( select parent_id,folder_name from folder union all select f.parent_id,r.concatenated_name +f.folder_name from folder f inner join recursiveCTE r on r.parent_id = f.id ) select folder_name from recursiveCTE