Handle NULL Parameter in SQL Server Table Valued Function

Question

CREATE FUNCTION fn_roles(@userid varchar(36))
RETURNS TABLE
AS
    RETURN
        SELECT * 
        FROM user_roles
        WHERE userid = @userid

My function accepts a parameter @userid and returns roles that the user is assigned to from the user_roles table.

What if I want to return all records from the user_roles table if a NULL value for the parameter is passed? What would be the most elegant way to handle that?

score 3 · Answer 1 · answered Feb 16 '20 at 18:23

3

Simply add it to the where clause with or:

select * 
from   user_roles
where  userid = @userid or @userid is null

answered Feb 16 '20 at 18:23

blackbishop

30,945
11
55
76

Ahmed Yousif · Accepted Answer · 2020-02-16T18:27:23.597

2

you can check parameter is null using ISNULL in this situation it will return userId so it will return all roles as following :

CREATE FUNCTION fn_roles(@userid varchar(36))
RETURNS TABLE
AS
RETURN
select * from  user_roles
where userid = ISNULL(@userid,userid)

edited Feb 16 '20 at 18:27

answered Feb 16 '20 at 18:21

Ahmed Yousif

2,298
1
11
17

I want to return ALL records from that table if NULL parameter is passed. What you suggest will return no records. – FNM Feb 16 '20 at 18:27
But the user wants to return *all* the rows. Also, adding the `ISNULL` would do nothing to chanbge the behaviour; nothing equals `NULL`, including `NULL`. – Thom A Feb 16 '20 at 18:27
This is more elegant than the previous one – James Feb 16 '20 at 18:31

score 1 · Answer 3 · answered Feb 16 '20 at 18:38

Not the least but just another option you can try this way also.

CREATE FUNCTION fn_roles (@userid VARCHAR(36))
RETURNS TABLE
AS
RETURN

SELECT *
FROM user_roles
WHERE @userid IS NULL
    OR (
        @userid IS NOT NULL
        AND userid = @userid
        )

How do I create a conditional WHERE clause?

Handle NULL Parameter in SQL Server Table Valued Function

3 Answers3