SQL Server - Checking for integer (id) within hierarchy of ids

Question

How would one go about checking whether a given ID is within a hierarchy of IDs? For example, let's say I have the following hierarchies in a self-referencing table called locations (e.g. these are hierarchical locations):

-- Declare the locations table
DECLARE @location_tbl TABLE(
    [location_id] int NULL,
    [parent_id] int NULL,
    [location_name] nvarchar(255) NOT NULL,
    [location_order] int NULL
)
-- Insert dummy data into locations table
INSERT INTO @location_tbl([location_id], [parent_id], [location_name], [location_order])
VALUES
(1, NULL, 'Location 1', 1),
(2, 1, 'Location 2', 2),
(3, 1, 'Location 3', 1),
(4, 1, 'Location 4', 3),
(5, 2, 'Location 5', 1),
(6, 5, 'Location 6', 1),
(7, 2, 'Location 7', 2),
(8, 7, 'Location 8', 1),
(9, 3, 'Location 9', 1),
(10, 9, 'Location 10', 1),
(11, 4, 'Location 11', 2),
(12, 4, 'Location 12', 1)

-- Show the locations table
SELECT * FROM @location_tbl;

-- Show how to get the hierarchy from the locations table, generally defined as a TBF
DECLARE @tree TABLE(
    [location_id] int, 
    [parent_id] int, 
    [level] int, 
    [location_order] int, 
    [location_name] nvarchar(255), 
    [location_name_hierarchy] nvarchar(255), 
    [row_number_hierarchy] nvarchar(255), 
    [location_id_hierarchy] nvarchar(255),
    [hierarchy_id] hierarchyid
);

WITH tree ([location_id], [parent_id], [level], [location_order], [location_name], [location_name_hierarchy], [row_number_hierarchy], [location_id_hierarchy]) AS
(
    SELECT A.[location_id], A.[parent_id], 0 AS [level], A.[location_order], A.[location_name],
        convert(varchar(max),A.[location_name]) AS [location_name_hierarchy],
        convert(varchar(max),right(row_number() over (order by A.[location_order], A.[location_id]),10)) AS [row_number_hierarchy],
        convert(varchar(max),A.[location_id]) AS [location_id_hierarchy]
    FROM @location_tbl AS A
    WHERE A.[parent_id] IS NULL

    UNION ALL

    SELECT B.[location_id], B.[parent_id], tree.[level] + 1, B.[location_order], B.[location_name],
        [location_name_hierarchy] + '/' + convert(varchar(max),B.[location_name]),
        [row_number_hierarchy] + '/' + convert(varchar(max),right(row_number() over (order by B.[location_order], tree.[location_id]),10)),
        [location_id_hierarchy] + '/' + convert(varchar(max),B.[location_id])
    FROM @location_tbl AS B 
    INNER JOIN tree ON tree.[location_id] = B.[parent_id]
)
INSERT INTO @tree([location_id], [parent_id], [level], [location_order], [location_name], [location_name_hierarchy], [row_number_hierarchy], [location_id_hierarchy], [hierarchy_id])
SELECT [location_id], [parent_id], [level], [location_order], [location_name], [location_name_hierarchy], [row_number_hierarchy], [location_id_hierarchy], 
cast('/' + [row_number_hierarchy] + '/' as hierarchyid) AS [hierarchy_id]
FROM tree

-- Location ID of interest
DECLARE @location_id int = 2
-- Show the tree
SELECT * FROM @tree

-- Filter the try by hierarchy containing Location ID of interest...how to do this properly?
SELECT * FROM @tree
WHERE [location_id] = @location_id OR [location_id_hierarchy] LIKE '%' + CAST(@location_id as nvarchar(255)) + '%'

I want to filter the tree where [location_id_hierarchy] contains the location id of interest (in this case, 2).

What I tried (and my thoughts):

The query I am using,

-- Filter the try by hierarchy containing Location ID of interest...how to do this properly?
SELECT * FROM @tree
WHERE [location_id] = @location_id OR [location_id_hierarchy] LIKE '%' + CAST(@location_id as nvarchar(255)) + '%'

does not work as it also returns a row that is not in the hierarchy (it contains 12 instead of 2 which it confuses with 2). I am trying to avoid doing another CTE or loop that will hurt performance, but I know that it is a possible solution. Is there something I can do like

WHERE @location_id IN SPLIT([location_id_hierarchy], '/')

? I know that last WHERE clause is totally wrong, but I'm thinking there must be something very simple and efficient to do this that I cannot see. This might be a regex-type question. Any ideas?

Answer 1

A brute force option would be to change your where clause to this:

WHERE [location_id_hierarchy] LIKE '%/' + CAST(@location_id as nvarchar(255)) + '/%'
 OR [location_id_hierarchy] LIKE CAST(@location_id as nvarchar(255)) + '/%'
 OR [location_id_hierarchy] LIKE '%/' + CAST(@location_id as nvarchar(255))

Not sure if that meets the "very efficient" requirement though.