SQL Server: User-Define Function w/Table Parameters Filtering

Question

I want to create a table-valued function that filters Table1 based upon values in Table2 but apply NO filtering if Table2 contains nothing.

I'm not good at SQL, so this is what I have thus far:

CREATE FUNCTION MyFunction(@table2 Table2 READONLY)
    RETURNS TABLE
        AS RETURN (
        SELECT userID, first_name, last_name
        FROM Table1
        WHERE EXISTS (SELECT user_ID FROM Table2 WHERE user_ID = Table1.user_ID AND user_security_group = 5)
)

In the above case, the function should return all users and their first/last names if they are in the user_security_group 5.

But if NO users exist within the user_security_group 5, I want to return ALL users (i.e. apply no filtering). So, pseudo code like:

CREATE FUNCTION MyFunction(@table2 Table2 READONLY)
    RETURNS TABLE
        AS RETURN (
        SELECT userID, first_name, last_name
        FROM Table1

        -- pseudo code here
        IF WHERE EXISTS (SELECT user_ID FROM Table2 WHERE user_ID = Table1.user_ID AND user_security_group = 5)
        -- THEN APPLY FILTERING
        -- ELSE DO NOT APPLY FILTERING

)

How do I achieve the desire functionality within MyFunction? Thank you.

P.S. - the above example is the simple case. Ideally, I'll be passing multiple tables as parameters into MyFunction and apply the same IF/ELSE logic to each parameter table.

Gordon Linoff · Accepted Answer · 2017-11-20 14:54:42Z

1

One way is with a second condition in the WHERE clause:

    SELECT t1.userID, t1.first_name, t1.last_name
    FROM Table1 t1
    WHERE EXISTS (SELECT 1 FROM Table2 t2 WHERE t2.user_ID = t1.user_ID AND t2.user_security_group = 5) OR
          NOT EXISTS (SELECT 1 FROM Table2 t2 WHERE t2.user_security_group = 5) ;

answered Nov 20, 2017 at 14:54

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Veljko89 Over a year ago

But that would return both cases, no? Think he should go with count first and then have IF statement

Gordon Linoff Over a year ago

@Veljko89 . . . Why not just let the SQL optimizer do the work?

NickBraunagel Over a year ago

@Veljko89 - what would your proposed COUNT + IF statement solution look like? Thank you.

NickBraunagel Over a year ago

@GordonLinoff I read your solution too quickly. Your OR statement does work, very cool approach; thank you.

Collectives™ on Stack Overflow

SQL Server: User-Define Function w/Table Parameters Filtering

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related