--Declare necessary variables
DECLARE @searchstring VARCHAR(20)
DECLARE @lensearch smallint

--Set values for search 
SELECT @searchstring = 'SQL'
SELECT @lensearch = LEN(@searchstring)

--Perform Search
SELECT DISTINCT P.ID, P.Item
FROM ##ProductParent P INNER JOIN ##ProductChild C ON P.ID = C.FKID
WHERE 
   C.ChildDescription LIKE @searchstring + '%'
   OR
   CHARINDEX(' ' + @searchstring, C.ChildDescription, 1) > 0
ORDER BY P.ID
   
DROP TABLE ##ProductChild
DROP TABLE ##ProductParent