Is Dynamic SQL in Your Stored Procedures Vulnerable to SQL Injection?

Pencil Bros. Geology, Inc. (a.k.a. Richard Dudley) asks: Is Dynamic SQL in Your Stored Procedures Vulnerable to SQL Injection? and then proceeds to provide a simple but effective CodeSmith template that generates a safe (and efficient) stored procedure for any table.