SQL Server Conditional statement in Where Clause

Question

I need to write a SQL query that retrieves records based on supplied RoleId.

e.g.

If RoleId = 1 THEN _t1.CreatedBy = @LoginUserId.

For other RoleId, I want all records from table without any condition on CreatedBy column.

I am providing sample query as per my requirement.

SELECT _t1.*
FROM [dbo].[TblPassDetail] _t1 WITH (NOLOCK) 
INNER JOIN [dbo].[TblVisitor] _t2 WITH (NOLOCK) ON _t1.VisitorId = _t2.SrNo 
WHERE _t1.LocationId = @LocationId
  AND _t1.CreatedBy = (CASE WHEN @RoleId = 1 THEN @LoginUserId ELSE 0 END)

Issue is that if supplied @RoleId is 1, I am getting all records for that LoginUserId, but if @RoleId is anything other than 1, then I am not getting any records as SQL checking records for _t1.CreatedBy = 0 which doesn't exist in the table.

Any suggestion on how to rewrite my query?

Tim Biegeleisen · Accepted Answer · 2017-11-23 10:57:45Z

1

You can rephrase the difficult part of your WHERE clause to this:

@RoleId = 1 AND _t1.CreatedBy = @LoginUserId
OR
@RoleId <> 1

In other words, if the RoleId is 1, then the logged in user must be the creator. But for other RoleId values there is no such dependence on the logged in user. Here is the full query:

SELECT _t1.*
FROM [dbo].[TblPassDetail] _t1 WITH (NOLOCK) 
INNER JOIN [dbo].[TblVisitor] _t2 WITH (NOLOCK)
    ON _t1.VisitorId = _t2.SrNo 
WHERE
    _t1.LocationId = @LocationId AND
    (
        (@RoleId = 1 AND _t1.CreatedBy = @LoginUserId) OR
        @RoleId <> 1
    );

answered Nov 23, 2017 at 10:57

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

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

1 Comment

Ganesh UP Over a year ago

Hi Tim, Thank you so much, my query worked perfectly by replacing CASE WHEN statement with the above 'AND-OR' combination. I am struggling with several combinations of CASE WHEN statement for this. Thanks Again. Have a nice day.

Rigerta · Accepted Answer · 2017-11-23 11:00:17Z

1

One way of achiving this would be to convert your CASE statement into an OR:

SELECT _t1.*
FROM [dbo].[TblPassDetail] _t1 WITH (NOLOCK) 
     INNER JOIN [dbo].[TblVisitor] _t2 WITH (NOLOCK) ON _t1.VisitorId = _t2.SrNo 
WHERE _t1.LocationId = @LocationId
      AND ( (_t1.CreatedBy = @LoginUserId AND @RoleID = 1) OR @RoleID <> 1)

Another way, kind of a workaround would be the following, but since LIKE is used, you would need to check the execution plan and performance of it:

SELECT _t1.*
FROM [dbo].[TblPassDetail] _t1 WITH (NOLOCK) 
     INNER JOIN [dbo].[TblVisitor] _t2 WITH (NOLOCK) ON _t1.VisitorId = _t2.SrNo 
WHERE _t1.LocationId = @LocationId
AND _t1.CreatedBy LIKE (CASE WHEN @RoleId = 1 THEN @LoginUserId ELSE '%%' END)

answered Nov 23, 2017 at 11:00

Rigerta

4,0591 gold badge17 silver badges28 bronze badges

1 Comment

Ganesh UP Over a year ago

Thanks Rigerta for your reply. I resolve my issue by using 1st approach.

Stanislav Kundii · Accepted Answer · 2017-11-23 10:57:11Z

0

Get all records when <> 1

(@RoleId = 1 AND _t1.CreatedBy = @RoleId) OR ISNULL(@RoleId,0)<> 1

answered Nov 23, 2017 at 10:57

Stanislav Kundii

2,9041 gold badge15 silver badges19 bronze badges

Collectives™ on Stack Overflow

SQL Server Conditional statement in Where Clause

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related