Second, why are you using dynamic SQL inside the stored procedure?  I am not seeing ANY reason for dynamic SQL to be used.  I am seeing a lot a poor coding and difficult to read because of the formatting of the code.  SQL Prompt errors trying to format the code.

A big chunk of this is a catch all query but you aren't using dynamic SQL to eliminate this aspect of the code.