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?

blackbishop · Accepted Answer · 2020-02-16 18:23:23Z

3

Simply add it to the where clause with or:

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

answered Feb 16, 2020 at 18:23

blackbishop

32.8k11 gold badges61 silver badges86 bronze badges

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

Comments

Ahmed Yousif · Accepted Answer · 2020-02-16 18:27:23Z

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, 2020 at 18:27

answered Feb 16, 2020 at 18:21

Ahmed Yousif

2,3481 gold badge13 silver badges17 bronze badges

3 Comments

FNM Over a year ago

I want to return ALL records from that table if NULL parameter is passed. What you suggest will return no records.

Thom A Over a year ago

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.

James Over a year ago

This is more elegant than the previous one

Suraj Kumar · Accepted Answer · 2020-02-16 18:38:57Z

1

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?

answered Feb 16, 2020 at 18:38

Suraj Kumar

5,6748 gold badges24 silver badges45 bronze badges

Collectives™ on Stack Overflow

Handle NULL Parameter in SQL Server Table Valued Function

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related