How to not use WHERE in a CASE statement?

SELECT
c.ClaimId, c.Number, s.ClaimStatusId StatusId, c.ClaimedAmount,
c.Created AS Lodged, c.Updated,
(u.UserName + '' + u.LastName) AS Creditor,
c.JobId, j.CompanyName AS JobName,
outcome.Approved AS ApprovedAmount, outcome.Rejected AS RejectedAmount,
s.NormalizedName AS Status, type.Name AS Type, c.Version, c.RemarksCount
FROM
ClaimView c
INNER JOIN
ClaimType type ON c.TypeId = type.ClaimTypeId
LEFT JOIN
ClaimOutcome outcome ON outcome.ClaimId = c.ClaimId
INNER JOIN
Job j ON c.JobId = j.JobId
INNER JOIN
ApplicationUser u ON u.Id = c.CreatedBy
INNER JOIN
ClaimStatus s ON s.ClaimStatusId = c.StatusId
WHERE
c.CreatedBy = @userId
AND c.StatusId > 1
AND c.IsDeleted = (CASE @isAdmin
WHEN 'False' THEN 0
END)


There's @isAdmin variable and I need to return date with @isAdmin IN (1,0) or to not use WHERE c.IsDeleted at all in case of @isAdmin is True. How I can implement it? That's my current code above.

@isAdmin

@isAdmin

AND

OR

case

WHERE

3 Answers
3

AND c.isDeleted = CASE WHEN @isAdmin = 'False' THEN 0 ELSE c.isDeleted END


You can use a simple OR to

OR

@isAdmin

false

@isAdmin='true'

c.isDeleted = 0

But you will not need both conditions to be true.

...
WHERE
c.CreatedBy = @userId
AND c.StatusId > 1
AND (@isAdmin != 'False' OR c.IsDeleted = 0 )


This should do. Might be overkill, but I think it's also more technically sound with the NULL handing:

(
AND (
@isAdmin = 'False'
AND c.IsDeleted = 0
)
OR
(@isAdmin <> 'False'
AND c.isDeleted IS NULL
)
)


Thanks for contributing an answer to Stack Overflow!

But avoid …

To learn more, see our tips on writing great answers.

Some of your past answers have not been well-received, and you're in danger of being blocked from answering.

Please pay close attention to the following guidance:

But avoid …

To learn more, see our tips on writing great answers.

Required, but never shown

Required, but never shown

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.