CODEWITHSUNDEEP
sqlsql-serverstored-procedures
mark vanzuela
mark vanzuela

Reputation: 1391

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

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.

Upvotes: 0

Views: 536

Answers (1)

Bhavik Goyal
Bhavik Goyal

Reputation: 2796

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

Upvotes: 3

Related Questions