Where Clause Using Conditional Statement

i have query below

SELECT @RoleUser = MR.Code FROM MasterRole MR INNER JOIN MasterUsersRole MUR ON MR.Id = MUR.RoleId
    INNER JOIN MasterUsers MU ON Mu.UserCode = MUR.UserCode
    WHERE MU.UserCode = @UserLoginID


select 1 Num
            , MyHistory.ID
         , MyHistory.RequestNumber
         , MyHistory.FlowID
         , MyHistory.FlowProcessStatusID         
    from
    (
        select *
        from Requests R
        inner join
        (
            --DECLARE   @UserLoginID nvarchar(200) = 'dum.testing.3'
            select distinct
                RequestID
            from dbo.RequestTrackingHistory RTH
            where IIF(@UserLoginID = 'admin', @UserLoginID, RTH.CreatedBy) = @UserLoginID 
                OR ( CreatedBy IN 
                    SELECT Mu.UserCode from MasterUsers MU
                        INNER JOIN MasterUsersRole MUR ON MU.UserCode = MUR.UserCode
                        INNER JOIN MasterRole MR ON MUR.RoleId = MR.Id
                        WHERE MR.Code = @RoleUser
                    )

                    )
                
        )            RT on R.ID = RT.RequestID
    )                                                      as MyHistory
    inner join MasterFlow                                  F on MyHistory.FlowID = F.ID
    inner join
    (
        select FP.ID
             , FP.Name
             , FP.AssignType
             , FP.AssignTo
             , FP.IsStart
             , case FP.AssignType
                   when 'GROUP' then
                       G.Name
               end as 'AssignToName'
        from MasterFlowProcess      FP
        left join dbo.MasterRole      G on FP.AssignTo = G.ID and FP.AssignType = 'GROUP'
    )                                                      FP on MyHistory.FlowProcessID = FP.ID
    inner join MasterFlowProcessStatus                     FPS on MyHistory.FlowProcessStatusID = FPS.ID
    left join MasterFlowProcessStatusNext                  FPSN on FPS.ID = FPSN.ProcessStatusFlowID
    left join MasterFlowProcess                            FPN on FPSN.NextProcessFlowID = FPN.ID
    left JOIN MasterRole                                   MR ON MR.Id = FPN.AssignTo
    left join MasterUsersRole                              MUR on MR.Id = MUR.RoleId
    left join MasterUsers                                  MURO on MUR.UserCode = MURO.UserCode
    inner join MasterUsers                                 UC on MyHistory.CreatedBy = UC.UserCode
    left join MasterUsers                                  UU on MyHistory.UpdatedBy = UU.UserCode
    LEFT JOIN RequestMT RMT ON MyHistory.ID = RMT.RequestID  
    LEFT JOIN RequestGT RGT ON MyHistory.ID = RGT.RequestID  
    left join (SELECT sum(QtyCU) countQty , RequestId  from dbo.RequestGTDetail where IsActive = 1 group by RequestId) RGTD on RGTD.RequestId = RGT.RequestId
    left join (SELECT sum(QtyPCS) countQty , RequestId  from dbo.RequestMTDetail where IsActive = 1 group by RequestId) RMTD on RMTD.RequestId = RMT.RequestId
    left join (SELECT COUNT(IIF(returnable = 0, returnable, null)) countReturnable , RequestId  from dbo.RequestMTDetail group by RequestId) RMTR on RMTR.RequestId = RMT.RequestId
    
    left JOIN dbo.MasterDistributor md ON md.Code = RGT.CustId or md.Code = RMT.CustId
    left JOIN dbo.MasterUsersDistributor MUD ON MUD.UserCode = MURO.UserCode AND  md.Code = MUD.DistributorCode 
    LEFT JOIN dbo.MasterReason MRMT ON RMT.ReasonId = MRMT.Id 
    LEFT JOIN dbo.MasterReason MRGT ON RGT.ReasonId = MRGT.Id
    LEFT JOIN dbo.MasterDistributorGroup MDG ON MDG.Id = MD.GroupId
    OUTER APPLY dbo.FnGetHistoryApproveDate(MyHistory.Id) AS x
    where REPLACE(FPS.Name, '@Requestor', uc.Name) <> 'DRAFT'
    AND MUD.DistributorCode IN (SELECT DistributorCode FROM dbo.MasterUsersDistributor WHERE UserCode = @UserLoginID)

i want to add some logic in where clause

this line

==> AND MUD.DistributorCode IN (SELECT DistributorCode FROM dbo.MasterUsersDistributor WHERE UserCode = @UserLoginID)

it depend on the @RoleUser variable, if @RoleUser IN ('A','B') then where clause above is executed, but if @RoleUser Not IN ('A','B') where clause not executed

i,m trying this where clause

AND IIF(@RoleUser IN ('A','B'), MUD.DistributorCode, @RoleUser) IN (SELECT DistributorCode FROM dbo.MasterUsersDistributor WHERE UserCode = IIF(@RoleUser IN ('A','B'), @UserLoginID, NULL))

it didn't work, only executed if @RoleUser IS ('A','B') other than that it return 0 record

any help or advice is really appreciated

thank you

The cleanest way I'm implemented these kind of crazy rules is a holderTable and a countVariable against the holder table.

I'll give a generic examples. This is a "approach" and "philosophy", not a specific answer....with complex WHERE clauses.

DECLARE @customerCountryCount int

DECLARE @customerCountry TABLE ( CountryName varchar(15) )


if ( "the moon is blue on tuesday" ) /* << whatever rules you have */
BEGIN 
    INSERT INTO @customerCountry SELECT "Honduras" UNION ALL SELECT "Malaysia"
END


if ( "favorite color = green" ) /* << whatever rules you have */
BEGIN 
    INSERT INTO @customerCountry SELECT "Greenland" UNION ALL SELECT "Peru"
END



SELECT @customerCountryCount = COUNT(*) FROM @customerCountry


Select * from dbo.Customers c 
WHERE

(@customerCountryCount = 0) 
OR
( exists (select null from @customerCountry innerVariableTable where UPPER(innerVariableTable.CountryName) = UPPER(c.Country)  ))
) 

This way, instead of putting all the "twisted logic" in an overly complex WHERE statement..... you have "separation of concerns"...

Your inserts into @customerCountry are separated from your use of @customerCountry.

And you have the @customerCountryCount "trick" to distinguish when nothing was used.

You can add a @customerCountryNotExists table as well, and code it to where not exists.

As a side note, you may want to try using a #temp table (instead of a @variabletable (@customerCountry above)... and performance test these 2 options. There is no "single answer". You have to "try it out". And many many variables go into #temp table performance (from a sql-server SETUP, not "how you code a stored procedure". That is way outside the scope of this question.

Here is a SOF link to "safe" #temp table usage.

Temporary table in SQL server causing ' There is already an object named' error