Including a Where clause based on parameters in Stored procedure

Question

I have a Parameter called @Codes passing in an optional comma delimited string of system codes.

If this parameter has a value I want to filter the query with a

WHERE 
  blah = blah AND
  Code IN ('Code1','Code2','etc.')

If the parameter is null or empty I want to omit the last where filter entirely.

Any ideas?

Answer 1

WHERE 
  blah = blah AND
  (
  Code IN (SELECT TheItem FROM dbo.fnSplit(@Codes))
  OR
  @Codes IS NULL
  )

To change the CSV into a recordset, fee free to choose one of the many functions. Personally, I tend to the a numbers table approach