Stored procedures are better then dynamic sql

SQL Add comments

kick it on DotNetKicks.com

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.

Conclusion

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% [?]

If you liked this article consider subscribing to my free rss article feed to automatically get new articles in your feed reader.

5 Responses to “Stored procedures are better then dynamic sql”

  1. Eric Friedman Says:

    There’s a few problems with stored procedures.

    There is no standard sproc language. They are tied to a specific DBMS.

    They don’t scale well.

    They are difficult to debug.

    I can only think of 2 good things about stored procedures. Since they reside in the database, they are good for queries that are database intensive like reporting applications. You’re not taking a performance hit from the network. Sprocs by their nature encapsulate the database code.

    Regards, Eric

  2. Justin Says:

    @Eric,

    I appreciate your points. I am aware of the short comings of SPs. In my case at work we are tied to SQL Server anyways for other reasons, so that is not a problem for me. Perhaps once I check out LINQ some more (or other ORMs) my opinion may change. Can you elaborate on them not scaling well and how dynamic sql scales better?

  3. Don Strawsburg Says:

    Justin,

    I don’t think your argument has any bearing on what whether Stored Procedures are better than dynamic SQL, other than what your personal preference is when working with MySQL or SQL Server. Eric is right, Stored Procedures unarguably tie you to a specific database, and I think your posting did establish that. Which is more than reason enough to qualify them as BAD. In most cases I have only met one group of people that like them, database administrators, because they tie you to the database. And can be controlled by them.

    All that said, yes there are instances that SP’s are faster, but who cares when you want to reuse code and the database is now DB2 and not SQL Server.

    ORMs like Hibernate are the way to go. But I’m not sure PHP has much support for ORMS, might, I’m just not a PHP guy.

    Thanks, Don

  4. Justin Says:

    Thanks for the comments Don. I admit that this is mostly just my preferences and realise many don’t like SPs. To me being tied down to a platform isn’t an issue, at least at work. One plus I forgot to mention, a plus for me, is when working in ASP.Net and SQL Server if I use SPs I can easily debug the SQL without re-compiling the the application.

  5. shift Says:

    I keep hearing this argument (last 15 years) that anything that ties you to a database is bad. In my 15 years I have never seen a company switch databases. If they are an Oracle shop they remain an Oracle shop. If they use MySQL they are unlikely to switch to SQL Server and so forth with PostgreSQL and every other database out there.

    I have no preference one way or the other but I’m tired of hearing X is bad because it ties you to Y. Database changes don’t happen that I have seen and any argument that X ties you to it should be left out of the argument.

    I mean sitting around the table planning something the next time that argument comes up, I will check with IT and see what the time frame is for dropping Oracle from our suite. It ain’t gonna happen folks.

Leave a Reply

WP Theme & Icons by N.Design Studio
Entries RSS Login