SQL Server - Conditional IN clause -

i trying select transactions table using in clause determined value of varchar parameter "@statuscode". if @statuscode 'all fail' want records status codes 1, 2, 3, or 5. otherwise want records status code equal numeric value of @statuscode. have not found elegant way other wrapping entire select statement in if condition.

i tried:

select * transactions (@statuscode = 'all failed' , statuscode in (1,2,3,5)) or (isnumeric(@statuscode) = 1 , statuscode = @statuscode) 

this compiles throws conversion error when pass 'all failed' since conditions aren't evaluated lazily.

so tried case:

select * transactions case @statuscode when 'all failed' statuscode in (1,2,3,5) else statuscode = @statuscode end 

but doesn't compile , gives syntax error @ 'in'.

is there way this? or stuck with

if @statuscode = 'all failed' begin select * transactions statuscode in (1,2,3,5) end else begin select * transactions statuscode = @statuscode end 

you can use coalesce trick if incoming variable null match on items -- this

  coalesce(@nullinputvar,matchingfield) = matchingfield 

but not work here because want match on 4 items. i've modified trick work (by first creating local variable null in special case) , might faster of other solutions have convert or cast every row. of course depends on how big table -- @ size solution faster. lot faster if statuscode field index.

declare @numcode integer;  if @statuscode = 'all fail' begin    set @numcode = null; end else begin    set @numcode = cast(@statuscode integer) end  -- magic:  select * transactions coalesce(@numcode,1) = statuscode    or coalesce(@numcode,2) = statuscode    or coalesce(@numcode,3) = statuscode    or coalesce(@numcode,5) = statuscode