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