CODEWITHSUNDEEP
sql-serversql-server-2012
Ganesh UP
Ganesh UP

Reputation: 107

SQL Server Conditional statement in Where Clause

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

e.g.

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?

Upvotes: 0

Views: 49

Answers (3)

Rigerta
Rigerta

Reputation: 4039

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)

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521639

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
    );

Upvotes: 1

Stanislav Kundii
Stanislav Kundii

Reputation: 2894

Get all records when <> 1

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

Upvotes: 0

Related Questions