>>>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
>
>
>(@comp_code char(4))
>RETURNS @result table (comp_code char(4))
>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.
If it's not broken, fix it until it is.
My Blog