>>>>>>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
>>>>
>>>>

>>>
>>>Make this function in-line table-valued function, it will perform better.
>>
>>I tried it that way at first.
>>As far as I could tell in line table-valued functions don't support if statements.
>
>It does not but you can use UNION ALL and put your checks into the first part of the union and the opposite check into the second part.

Thank you, Naomi
I'll try it