CODEWITHSUNDEEP
sqlsql-server
NickBraunagel
NickBraunagel

Reputation: 1599

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

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.

Upvotes: 1

Views: 43

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269973

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

Upvotes: 1

Related Questions