I have about 100 e-mail address like I am sure that everyone else does as well now a days. Many of them I never check, but today I got an e-mail from a friend that I use to work with. I met this friend about 10 years ago when we worked at the same company. I am sure that his first memory was of me taking all the admin access away from about 15 developers (Not a great day in my career). Back then I was very adamant that all code must be done in stored procedures, he wanted to know how I felt about it today. Today I know that I am not always going to be so lucky, however that does not mean that I cannot share my opinion on a professional level.
So I took 5 min to write down what first came to my mind on the Pro’s and Con’s of using stored procs:
- There is a lot of flexibility in using dynamic SQL statement.
- Developers have more control over how data is returned.
- When code is embedded in web pages the risk of SQL Injection is very high. Over the last few years we have seen actual examples from clients who have not changed over to Proc’s What use to be a matter of just sanitizing your inputs is now a matter of being able to catch the hack at all. Many of the hack are coming across as Hex so there is no way to sanitize. I have actual code examples of this.
- There is always the fact that SQL Server has no idea what is coming at it, so it has to continue to compile the procs and look at Stats to see what is the best index to use.
When the code is not in procs the performance tuning is left up to the developer of the application or it put a major load on the DBA trying to find the bad code using 3rd party procs and or trace files and performance monitors
Security is a concern when direct table access is allowed. Even in a Select statement. There have been many times when I have seen a Select statements hurt the performance of a production server.
I am sure I missed a bunch of Pro’s and Con’s. If you make a note on here I would be more than happy to add them.