Rip's Domain

Opinion: To Use Or Not To Use Stored Procedures?

Posted in ColdFusion, Rants, SQL by rip747 on November 15, 2007

I was browsing around on Reddit today and came across an article that particular struck my interest: 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.


One Response

Subscribe to comments with RSS.

  1. Matthew said, on December 2, 2007 at 4:14 pm

    I would completely have to agree with you there. I also see them fading out over time and just becoming another basic tag that sites within the ColdFusion library. I to at one point programmed all my projects with stored procedures and would only use a cfquery tag to do query of queries. Lately I have been reading posts such as your better assuring that there is not a noticeable performance different and that has sent me back the direction of just writing data cfc’s that will act as my “stored procedures” in my ColdFusion applications.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: