Opinion: To Use Or Not To Use Stored Procedures?
What intrigued me was that I have been a proponent of stored procedures in the past and now avoid them like the plague. I think the switch came when I started to realize how difficult and time consuming it was to write and maintain a project using stored procedures versus a project that just use parametrized queries. I also remember how difficult it was to write dynamic sql statements within a stored procedure.
I think really the only reason I used them in the past was because CF at the time didn’t support cfqueryparam and it as the only way to protect yourself against SQL injections. Once cfqueryparam came onto the seen, it took awhile for me to adopt it in my code and actually realized the benefits. Now I could write my SQL statements within CF and still have the security of a stored procedure protecting me against SQL injections. It was a blissful time; I could really pound out projects and code and debugging an application became a snap.
There were a few still out there that still voiced the myth that using stored procedures gave you a speed boost over
parameterized queries. Those people were soon put to rest when most RDMS and ODBC drivers made it so that using either way gave you the speed benefits of caching the query plan. Now this point is mute.
Today, I silently laugh at people that still think that using stored procedures in projects as the main way to communicate with their database. By creating views, using cfqueryparam and knowing the proper ways of using the different joins available; I can mimic almost any behavior a stored procedure can do when it comes to pulling data from a database.
To me I see the stored procedure in CF going the way of CFX tags… away.