Assuming I'm ready your question correctly, this should do the trick:
SELECT * FROM yourTable
WHERE (@whereType <> 1 OR Duration > 0)
AND (@whereType <> 2 OR Duration = 0)
but be careful with this sort of construct if there table contains any significant number of rows: you will be forcing a table scan of yourTable
unless you have other filtering clauses for which there are usable indexes.
Edit: Some will find Justin's answer clearer to read (the two acheive the same result), though I prefer to keep the OR
clauses inside the brackets as I find the logic easier to follow especially when other filtering clauses are added. In either case the warning about index use and scanning holds.
For Large Tables
Neither this or the other arrangement is suitable for large tables (as your comments suggests you have noticed) unless there are other filtering clauses that dramatically limit the amount of rows the dynamic filter acts upon.
Within a stored procedure you have the option of ad-hoc SQL via EXEC
:
SET @SQL = 'SELECT * FROM yourTable '
IF @whereType = 1 THEN @SQL = @SQL + 'WHERE Duration > 0'
IF @whereType = 2 THEN @SQL = @SQL + 'WHERE Duration = 0'
EXEC (@SQL)
Be very careful with this pattern. You can easily open yourself to injection attacks (though this example doesn't) with ad-hoc SQL.
See the article mentioned in Andy's answer for a very detailed discussion of the options available and the issues (with regard to performance, maintainability, and security) you might encounter with each. Erland's other articles are very useful, thorough, and well written too, see http://www.sommarskog.se/index.html (the first one, regarding dynamic SQL, is relevant here too).