If in the join t-sql

Question

i have a big t-sql stored procedure with this part that i am concerned about:

insert into @list
(
params
...
) 
select params
,..
,oip.someAttr
from @list2 as O
join dbo.abc oip
on oip.id = O.id
AND oip.prgId <> 4
join .. someother tables
...
where oipr.attrA = 1232

Now i want to write a logic that want for an additional parameter in the stored procedure "@value bit" if it is set then only the "AND condition above" should be checked otherwise not.

so if i do like

join dbo.abc oip
on oip.id = O.id
[AND]??? (i donot know).

Thanks in advance :)

Answer 1

Assuming that oip.prgId cannot possibly hold 0s, here's another option to consider/try:

AND oip.prgId <> 4 * @value

If @value is 1, the right part of the inequality evaluates to 4, and so the corresponding rows will be filtered out. If @value is 0, however, then the expression becomes equivalent to oip.prgId <> 0, which would essentially mean any value of oip.prgId (since we've assumed that there can't be 0 values in the column).

Answer 2

This should do it: AND (oip.prgId <> 4 OR @value = 0)