Are there design patterns/best practices in writing dynamic SQL in stored procedures?
Best is to read The Curse and Blessings of Dynamic SQL by  Erland Sommarskog.

Also read Using Dynamic SQL in Stored Procedures and Stored Procedures - Executing &amp; Dynamic SQL