Conditional comparison, compare database column with parameter if parameter is greater than 0

Question

I have a stored procedure (SQL SERVER) which accepts an @IdParameter variable (Id is non nullable but can be 0). It is a simple stored procedure which selects all fields from a database table. My requirement is that if the @IdParameter is greater than 0 then the Id field of database table is to be compared with @IdParameter else we don't need the comparison.

I tried using CASE etc but I am unable to solve my issue

I need something like

SELECT * 
FROM TABLE_NAME 
WHERE [Status] = 1 
  AND (CASE WHEN @Parameter > 0 THEN Id = @Parameter END)

Answer 1

CASE returns a scalar value not a boolean result. You shouldn't, however, be using a CASE here at all but instead an OR. As, however, you also therefore have a "catch-all" or "kitchen sink" query, then you should also add RECOMPILE to your OPTION clause to stop you suffering (severe) parameter caching:

SELECT <explicit column list>
FROM dbo.TABLE_NAME 
WHERE [Status] = 1 
  AND (@Parameter = ID OR @Parameter IS NULL)
OPTION (RECOMPILE);

I, personally, prefer to use NULL instead of an arbitrary value (like 0) for the "catch-allness", which is what I've done above.