Stored.Procedures

I have always been a fan of dynamic SQL over stored procedures, simply because I felt that I have greater control over what SQL statements I would like to generate in code. And over the years, the gap between the benefits of dynamic SQL vs. stored procedures has also narrowed down.

Note: Now when I say dynamic SQL, I mean the one that uses named parameters ;)

But my current project has made me realized an important benefit of using stored procedures and that is ... when trying to upgrade from a very old SQL Server to a new version, it is difficult to identify which embedded SQL statement will break and it is even more difficult if the SQL statements are built using string concatenation and embedded in legacy VB6 code with no unit test.

Now, the SQL Upgrade Advisor tool will not be able read and 'advise' on these embedded SQL. So, in such scenario, I think using stored procedures would have been much better.