Case statement in where clause in SQL Server if variable have any value otherwise compare current

Question

I have 2 tables Account and Roles. I want to get all users whether they have any role or not. But when I run below query it returns only users which have any role in the Roles tables, but it does not return users which don't have any role.

I'm checking if there is any value in @role_id variable then it compare with @role_id otherwise compare with current value. Is it correct?

SELECT 
    a.user_id, a.username, a.first_name, a.last_name, a.division_id,   
    a.dept_id, a.email, a.password, a.IsAdmin, a.status, a.cdate, a.mdate 
FROM 
    Account AS a 
LEFT JOIN 
    Roles r ON a.user_id = r.user_id
WHERE 
    r.role_id = (CASE 
       WHEN @role_id IS NOT NULL AND @role_id <> '' THEN @role_id ELSE r.role_id 
    END)

Answer 1

try this way:

SELECT a.user_id, a.username, a.first_name, a.last_name, a.division_id, a.dept_id, a.email,
a.password, a.IsAdmin, a.status, a.cdate, a.mdate FROM Account AS a 
LEFT JOIN Roles r on a.user_id = r.user_id
WHERE ((ISNULL(@role_id,'') = '') OR (r.role_id = @role_id))