When choosing dynamic, parameterized SQL statements versus stored procedures, there are no technical advantages of one over the other.

Performance was mentioned. However, dynamic, parameterized SQL statements are just as efficient as the same code running in a stored procedure, especially with MSSQL. Execution plans are cached for parameterized SQL and stored procedures.

SQL injection was mentioned. Dynamic, parameterized SQL statements are no more susceptible to this than a stored procedure.

The key is to use parameterized SQL. That is...

BAD BAD BAD

"Select * From MyTable Where ID = " & userID

GOOD GOOD GOOD

"Select * From MyTable Where ID = @user-id"

If you use the latter form, that will perform the same and be just as safe as using a stored procedure. Don't allow performance and injection attacks to be a factor in the decision on which technique to use.