>>Maybe this is the question I should have asked:
>>
>>Table company has a comp_code column - a primary key and a comp_group column
>>comp_group can be blank or it can contain the key to itself or another row in the table.
>>
>>How can I find all the members of the group that @comp_code belongs to?
>
>This is still not very clear. Can you post a sample input and desired output? Are you looking for recursive cte?

In my earlier reply, I showed how I found the members of a group.

That turned out to be the easy part. Getting that into a function that I could call from code turned out to be quite a brain-twister because it contains an IF statement.

After hours of browsing, I found some syntax on line that showed me how to do it with a table-valued function.
With that function in hand, the changes to the app were trivial.

IF OBJECT_ID(N'dbo.GetGroupMembers', N'TF') IS NOT NULL
    DROP FUNCTION dbo.GetGroupMembers;
GO
CREATE FUNCTION dbo.GetGroupMembers 
-- Table valued function
 
(@comp_code char(4))                     -- input parameter name and data type
RETURNS @result table (comp_code char(4)) -- return type
AS
BEGIN
         
IF EXISTS (SELECT * FROM company WHERE comp_code = @comp_code AND compgroup <> ' ' ) 
  BEGIN
    INSERT INTO @result
    SELECT a.comp_code FROM
    (SELECT * FROM company WHERE compgroup = (SELECT compgroup FROM company WHERE comp_code = @comp_code)) A
  END
ELSE
  BEGIN
    INSERT INTO @result
    SELECT @comp_code 
  END
  RETURN 
END
GO