SQL query in stored procedure; create a query with condition special parameter

Question

Suppose I have tables Users, Roles and UserRoles (association). Then I want to create a stored procedure with a parameter userid to return all the roles of that certain user. However if the UserId is 1 (special Id), all roles should be returned.

My query on the SP should be like this without the special condition:

SELECT r.* 
FROM Roles 
INNER JOIN UserRoles ur ON r.Id = ur.RoleId 
WHERE ur.UserId = @userId;

How would I change this query to cater what I wanted? Or is this possible on a single query alone? I would not want to use IF..ELSE and use 2 queries.

Any comments are welcome.

ypercubeᵀᴹ · Accepted Answer · 2011-05-09 05:16:17Z

3

Hi this will return all the user roles when @userId = 1

SELECT r.* 
FROM Roles 
INNER JOIN UserRoles ur
  ON r.Id = ur.RoleId
WHERE ur.UserId = @userId 
  OR @userId = 1

edited May 9, 2011 at 5:16

ypercubeᵀᴹ

116k19 gold badges181 silver badges249 bronze badges

answered May 9, 2011 at 4:54

Bhavik Goyal

2,7966 gold badges25 silver badges42 bronze badges

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

Collectives™ on Stack Overflow

SQL query in stored procedure; create a query with condition special parameter

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related