sql server - Conditional Operator in SQL Where Clause

Question

I'm wishing I could do something like the following in SQl Server 2005 (which I know isnt valid) for my where clause. Sometimes @teamID (passed into a stored procedure) will be the value of an existing teamID, otherwise it will always be zero and I want all rows from the Team table.

I researched using Case and the operator needs to come before or after the entire statement which prevents me from having a different operator based on the value of @teamid. Any suggestions other than duplicating my select statements.

    declare @teamid int
    set @teamid = 0

    Select Team.teamID From Team
      case @teamid
         when 0 then 
            WHERE Team.teamID > 0
         else
            WHERE Team.teamID = @teamid
      end 

Answer 1

You can do that without a case:

SELECT  Team.teamID 
FROM    Team
WHERE   (@teamid = 0 AND Team.teamID > 0)
        OR (@teamid <> 0 AND Team.teamID = @teamid)