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 

make use of a recursive query to traverse the mother and father after which this method for concatenating right into a string.