query conditional

johnkainn
johnkainn used Ask the Experts™
on
Parameters:  @UserId, @RoleId

In a Table I have the columns:  Id, MyText,  AccessId, AuthorId

AccessId can have value 1,2, 3.  
If AccessId is 1 the row should be returned.
It it is 2,  then only return row if @UserId=@AuthorId
If it is 3,  then only return row if  @RoleId=5.

Can you please help me write a query for this?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
hi johnkainn,

Try this
SELECT *
FROM tablename
WHERE
  AccessID=1
  OR (AccessID=2 AND AuthorID=@UserID)
  OR (AccessID=3 AND @RoleID=5)

Open in new window

Commented:
That is correct condition.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial