How to use case when in where conditon tsql?

Following is the part of a stored procedure:-

开发者_Python百科@ID int
@Col1 int
@Col2 int

Select * 
from Table_01 t1
JOIN table_02 t2 on t1.col = t2.col
where 
  1 = case when @Col1 = 0 then 1 else (case when t2.Col1 = @Col1 then 1 else 0 end) end
  AND 1 = case when @Col2 = 0 then 1 else (case when t2.Col2 = @Col2 then 1 else 0 end) end
  AND 1 = case when @ID = 0 then 1 else (case when t2.ID = @ID then 1 else 0 end) end

Currently

• if @ID = 0 then it ignores the condition
• if @ID has value then it returns results based on the criteria satisfaction

I need to add another condition there :-

if @ID = -1 then should not return any result (coz there is no match) but it should act like @ID = 0 (ignore to add expresssion)

Am I clear? :(

Select ...
From Table_01 As t1
    Join table_02 As t2 
        On t1.col = t2.col
Where Case 
        When @ID = 0 Then 1 
        When t2.ID = @ID Then 1 
        When @ID = -1 Then 0
        Else 0 
        End = 1

If this last condition should trump the other two, then you need to change the order:

Select ...
From Table_01 As t1
    Join table_02 As t2 
        On t1.col = t2.col
Where Case 
        When @ID = -1 Then 0
        When @ID = 0 Then 1 
        When @ID = t2.ID Then 1 
        Else 0 
        End = 1

Of course another way which may perform better (assuming @ID = -1 trumps):

Select ...
From Table_01 As t1
    Join table_02 As t2 
        On t1.col = t2.col
Where @ID != -1 
    And ( @ID = 0 Or @ID = t2.ID )

Update based on edit of OP

It sounds like you are trying to do something like the following:

Select ...
From Table_01 As t1
    Join table_02 As t2 
        On t1.col = t2.col
Where @ID != -1
    And ( @Col1 = 0 Or @Col1 = t2.Col1 )
    And ( @Col2 = 0 Or @Col2 = t2.Col2 )
    And ( @ID = 0 Or @ID = t2.ID )

I would try to write it so it is clearer:

WHERE @ID IN (0, t2.ID) AND @ID <> -1

or

WHERE (@ID = 0 OR t2.ID = @ID) AND @ID <> -1

If t2.ID can not be -1 then you don't need that part.

If I understand you correctly, you simply want @ID = -1 to have the same effect as @ID = 0. In that case here is how your query might look like:

Select * 
FROM Table_01 t1
  JOIN table_02 t2 on t1.col = t2.col
WHERE @Col1 IN (0, t2.Col1)
  AND @Col2 IN (0, t2.Col2)
  AND @ID IN (-1, 0, t2.ID)