CODEWITHSUNDEEP
sqlt-sql
Majid821
Majid821

Reputation: 105

Use if condition in where clause of sql query

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 ??

Upvotes: 0

Views: 169

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269573

This is usually handled using or:

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

Upvotes: 2

SAS
SAS

Reputation: 4035

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)

Upvotes: 1

Popeye
Popeye

Reputation: 35900

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!!

Upvotes: 1

Related Questions