CODEWITHSUNDEEP
sqlsql-serverfunctionparametersnull
FNM
FNM

Reputation: 59

Handle NULL Parameter in SQL Server Table Valued Function

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?

Upvotes: 1

Views: 1431

Answers (3)

Suraj Kumar
Suraj Kumar

Reputation: 5653

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?

Upvotes: 1

Ahmed Yousif
Ahmed Yousif

Reputation: 2348

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)

Upvotes: 2

blackbishop
blackbishop

Reputation: 32680

Simply add it to the where clause with or:

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

Upvotes: 3

Related Questions