Members

Technology Zones

IBM Learning Center

Articles

Hosted By

MaximumASP

Info
Rated
Read 36,608 times
5 comments

Contents

Related Categories

To SP or not to SP in SQL Server - The cross-platform tie-in argument

Douglas Reilly

The cross-platform tie-in argument

The cross-platform tie-in argument

One argument against using SPs is that they tie you to a particular database platform. But is that always true? Neither Rob nor Frans address this issue in the posts I reference.

I know of one developer who created an application that can be used in two modes. The program can be operated on a LAN with all users hitting the same SQL Server which uses SPs extensively), or it can be operated independently, where the database is Microsoft Access (which does not use SPs). The program runs all database access through a known API supported by a pair of pluggable dynamic link libraries. There is added complexity, since changes need to be made to both code bases at the same time. For this application, the tradeoffs were well worth it.

Can an application be too flexible? The answer is yes. If you don't use features of your chosen database platform because you might need to move to a different database someday, you are probably giving up too much. I have built many applications that had to account for the possibility that someday a different database back end might be required. I have never been called to make that retrofit. If multiple back ends are not an initial requirement, I would not worry much about it. I would, of course, partition the application so that database access is handled by classes separate from the presentation logic, but that is a good idea no matter what.

Cross platform tie-in: Ad-hoc SQL has the advantage here, but do you care, and is there a good way to overcome the limitation anyway?

Conclusion

So, should you use SPs or ad-hoc SQL? The answer is “it depends.” I have placed myself firmly on the side of doing all database access through SPs. I do so knowing that I am not getting any unique security benefits using SPs, knowing that the performance benefits are not as clear cut as I once might have thought (but are still real in some cases), knowing how to leverage SPs to minimize the maintenance load, and understanding that I am more tied to SQL Server than I might be if I were to use ad-hoc SQL. What do you think?

###

Doug has a blog post on this article, and comments or questions can be posted there: http://weblogs.asp.net/dreilly/archive/2005/03/30/396251.aspx

Douglas Reilly is the owner of Access Microsystems Inc., a small software development company specializing in ASP.NET and Mobile development, often using Microsoft SQL Server as a database.

Comments

Read Comments | Post Comment

  • Good article

    Posted by drsql on 02 Sep 2005

    [quote]I have a number of situations in which users can have access to only certain rows of a database. The rules covering which rows a user can access are often complex and best managed in procedural...

  • Orm Rocks - and also answers some of the questions

    Posted by quickyresponse on 02 Sep 2005

    Orm.Net (and others like it) sort of takes the Ad-Hoc queries to the next level - basically they allow you to do a Neo-esque automated generation of the queries simply by reading the DB schema. So a d...

  • Posted by quickyresponse on 02 Sep 2005

    You've done two important things (or more)

    1. You've proven that it takes a little more know-how to write code that prevents sql injection - as noted in the article

    2. You've added a bunch of li...

  • SP's save you time and money

    Posted by brianfairchild on 30 Aug 2005

    When developing a data access method you generally write some sort of skeleton code that is just enough to get data in and out of your database, knowing that later on you will comeback and add error c...

  • Posted by ryanbost on 31 Jul 2005

    You don't have to check the textbox for illegal characters if you use stored procedures correctly. See post above.