Reasons why one may choose to keep all SQL in separate stored procedures:
- In the case where applications are hosted by clients (rather than centrally) it is possible to send clients SQL scripts to load into their database.
- You can run a debugger on SQL which lives in a stored procedure. I don’t know of any debugger which works on SQL-as-strings-in-C#.
- It’s slightly easier to track changes in version control, because you can see directly to the SQL and changes aren’t obfuscated by the wrapping in the containing language.
- Drawing on experience, you can make an educated guess that certain methods you are about to write will run faster if their SQL lives as a stored procedure.
Personally, I don’t like stored procedures very much. Arguments against the above points:
- It’s easier to determine which clients have which code when the whole system is given one version number. If each client has zero to many SQL scripts hanging around, each with different version numbers, things get messy.
- If you keep your SQL simple, you won’t have to debug it. Just write unit tests for it. When it passes, you won’t have to come back to it again, because it’s simple. SQL is not a pleasant language when you start wanting to do anything complex, so don’t.
- If your SQL is simple, you won’t be changing it much. Would you put your regular expressions in separate files?
- Your SQL may run faster as a stored procedure, but is that faster than ‘dynamically generated’ or parameterised SQL? I’m willing to bet you don’t see a significant speed increase when using a stored procedure instead of parameterised SQL.