Use if condition in where clause of sql query

Question

I have a query that is:

SELECT  DISTINCT DepotIo.Depot2Guid AS Depot1Guid, Depot2.Title, NULL AS Depot2Guid
FROM DepotIo
 JOIN DepotIoDetail ON DepotIo.Guid = DepotIoDetail.DepotIoGuid
 JOIN dbo.GetUserDepot(@UserGuid) AS Depot2 ON DepotIo.Depot2Guid = Depot2.Guid
 JOIN Item ON Item.Guid = DepotIoDetail.ItemGuid
WHERE DepotIo.Company = @Company AND (DepotIo.Branch = @Branch)

But I want to when @Branch is not null, comes to WHERE condintion part and when it's value is null, relinquish it.. Like this :

WHERE DepotIo.Company = @Company AND (CASE @Branch 
WHEN IS NOT NULL THEN DepotIo.Branch = @Branch)

what's true command ??

Can DeDepotIo.Branch be null?

SAS
– SAS

2019-09-04 10:08:40 +00:00
Commented Sep 4, 2019 at 10:08 — SAS
– SAS, Commented Sep 4, 2019 at 10:08

Gordon Linoff · Accepted Answer · 2019-09-04 10:45:54Z

2

This is usually handled using or:

WHERE DepotIo.Company = @Company AND
      (DepotIo.Branch = @Branch OR @Branch IS NULL)

answered Sep 4, 2019 at 10:45

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Comments

Popeye · Accepted Answer · 2019-09-04 10:09:03Z

1

You can use CASE WHEN as follows:

(CASE WHEN @Branch IS NOT NULL 
THEN CASE WHEN DepotIo.Branch = @Branch THEN 1 ELSE 0 END
ELSE 1 END = 1)

Cheers!!

answered Sep 4, 2019 at 10:09

Popeye

36k4 gold badges12 silver badges31 bronze badges

Comments

SAS · Accepted Answer · 2019-09-04 10:27:54Z

1

If I understand the question, this should do it. If DeDepotIo.Branch is not nullable. This will relinquish rows where @Branch<>DeDepotIo.Branch, but not when @Branch is NULL.

WHERE DepotIo.Company = @Company AND (ISNULL(@Branch, DeDepotIo.Branch) = DeDepotIo.Branch)

edited Sep 4, 2019 at 10:27

answered Sep 4, 2019 at 10:06

SAS

4,0852 gold badges33 silver badges49 bronze badges

Collectives™ on Stack Overflow

Use if condition in where clause of sql query

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related