FWIW those two queries do not produce the same results.

Consider the following when your string to split is not null.

--Original Query Approach

DECLARE @pCustomerID int,

@pUserID int,

@pOUIDs varchar(max) = '367',

@pLocationIds varchar(max)

SET@pCustomerID = 14

SET@pUserID = 1

--SET@pOUIDs = NULL

SET@pLocationIDs = NULL

SELECT DISTINCT ou.OUID

FROM

(SELECT

ouChild.OUID,

ouChild.CustomerID,

ouChild.Enabled,

ou.OUID as OUIDAncestor

FROM #Hierarchy ou

INNER JOIN #Hierarchy ouChild ON ouChild.Hierarchy + '.' LIKE ou.Hierarchy + '.%'

)ou

WHERE

ou.CustomerID = @pCustomerID

AND ou.Enabled = 1

AND

(

@pOUIDs IS NULL

OR

@pOUIDs LIKE '%,' + CAST(OUIDAncestor AS VARCHAR(20)) + ',%'

);

-- CTE Approach

;WITH LocationHierarchy As

(

SELECT

ouf.OUID,ouf.OUParentID,ouf.CustomerID,ouf.Hierarchy,ouf.Enabled

FROM

#Hierarchy ouf

Where

OUID in (select val from dbo.Split(@pOUIDs,',')) or @pOUIDs is null

UNION ALL

SELECT

ouc.OUID,ouc.OUParentID,ouc.CustomerID,ouc.Hierarchy,ouc.Enabled

FROM

#Hierarchy ouc INNER JOIN

LocationHierarchy on ouc.OUParentID = LocationHierarchy.OUID

Where

ouc.Enabled = 1 and ouc.CustomerID = @pCustomerID

)

Select

DISTINCT ou.OUID

from

LocationHierarchy ou

The results are different in the two queries so your comparison is even further off track. 😉