Has mostly to do with security.
Some organizations want to keep the developer as far from the database as possible.
Then is using a stored procedure an option because only those who have SQL Server administrator rights on the SQL server can modify those.
With the exception of an IBM DB2 database server there is not much difference between using dynamic SQL transacts scripts or stored SQL transact scripts.
Linq to SQL creates for instance completely dynamically SQL transact scripts.
If it are procedures like you wrote, then I like more stored procedures, but that is only because they are more easy to debug themselves than often with quotes messed dynamic scripts