There are many arguments to whether stored procedures or dynamic sql is best when developing applications. I don’t think there is a cut and dry “best”. As with most things in programming, it boils down to what is best in your current situation.
A case for stored procedures
I have worked with stored procedures and dynamic sql extensively on several different platforms. When working in C# or ASP.Net I like to use stored procedures for most of my database access. However with all technologies there are limitations and times when a different approach is required. When a stored procedure cannot easily complete the task I look to dynamic sql.
I like stored procedures for a couple of reasons. Firstly, any values I pass in are automatically parameterized and sanitized for me so I don’t have to worry about special characters (like ‘) or sql injections (for the most part). Second, all the sql code is in one repository and I know exactly where to look when faced with an sql error and the procedures are easily reused saving on duplication if I had used dynamic sql.
A case for dynamic sql
When I am working in PHP and MySQL I always use dynamic sql. I do this also for a couple of reasons. My PHP applications tend to be on a much smaller scale then when I work in C#. This is because I use C# at work where we build enterprise applications and I use PHP at home for personal projects. I also use dynamic sql when working in PHP because my hosting is on CPanel which gives me access to my MySQL database with phpMyAdmin which does have a nice interface for stored procedures like SQL Server. Dynamic sql is just easier here. Although there are a few more things to deal with when generating dynamic sql it is very flexible and is only limited by your platforms implementation of SQL. Flexibility is always a huge plus.
So I stand currently on stored procedures as my favorite method right now. Perhaps I’ll get ambitious and look more closely at stored procedures in MySQL. I have played a bit with NHibernate and LINQ for C# which have solid benefits that may sway my opinion once I have used them more.
Popularity: 29% [?]