Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
Brain Freeze? or is it possible
Message
 
 
To
13/01/2016 16:42:54
General information
Forum:
Microsoft SQL Server
Category:
SQL syntax
Miscellaneous
Thread ID:
01629732
Message ID:
01629827
Views:
37
>>>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.
If it's not broken, fix it until it is.


My Blog
Previous
Next
Reply
Map
View

Click here to load this message in the networking platform