This has been a topic of debate for a long time. If you think this discussion is trivial, you need to evolve. Read on if you
think you got something to think. Practically developers, and designers prefer to use stored procedures simply because of one of the following reasons:
- Code looks good! They get a feeling that at least they have done something right in their life. They keep ridiculing others for having used in-line queries. They once read somewhere that SPs are the way to go.
- The above category is the follower - mindless follower. They are hard to convince and they will follow the same practice for years. Never mind. They also came to know in their college days that SPs are "precompiled", "fast", and "secure". They won't be able to tell you the whys and hows of things.
- Some experienced and good developers attribute the usage of SPs over parametrized queries by saying that it saves you a lot of Network bandwidth, and gives you a single point of failure. You know where things can go wrong.
- The flag bearers of business-logic-in-application-language-like-C# will tell you that the purpose of an RDBMS is to store data, and the act of manipulating it should be done on a higher level language. Languages like C# arm you with better weapons than T-Sql could ever do. T-Sql is Set based and Procedural thus monolithic. You are not living in caves anymore, are you?
- What about Sql injection attacks? They are dreaded weapons of hackers. How does parametrized query work this out? Well, they are as safe as SPs. Both are parametrized, aren't they? Its different that in-line queries. In-line queries are prone to attacks. However, there are ways to break into SPs as well, so you are not safe anyways!
- Are you talking about doing away with SPs altogether? Certainly not.
You need to decide whats best for the purpose. CRUD operations doesn't seem logical to me anymore. Imagine having a about 100 normalised tables in your database. Would you like to have 100*4 SPs doing the normal Insert, Update, Select and Delete for you? Doesn't seem so convincing. Moreover, the performance benefits of SPs and queries have been diminishing since Sql Server 7.0.
As per books online:
SQL Server 2000 and SQL Server version 7.0 incorporate a number of
changes to statement processing that extend many of the performance
benefits of stored procedures to all SQL statements. SQL Server 2000
and SQL Server 7.0 do not save a partially compiled plan for stored
procedures when they are created. A stored procedure is compiled at
execution time, like any other Transact-SQL statement. SQL Server 2000
and SQL Server 7.0 retain execution plans for all SQL statements in the
procedure cache, not just stored procedure execution plans.
- If you are planning to be object based and enjoy the rich benefits of C# as a language you must consider LINQ, or any other efficient O/R mapper like NHibernate. You will start loving it. You will love the feeling of having used pragmatic best practices and approaches.
- If you have Business Logic written in C# it implies that you can do Unit Testing with Code Coverage. If you need to traverse the rows its much easier, as it doesn't have to create #Temp tables in tempDB for Cursors. The DBA is no longer required to write complicated, hard to maintain thousands of lines of monolithic code. He can focus on database management, fine tuning, backup, replication and other important stuff! You don't need to explain the business to your DBAs. Let them be specialised in what they are doing.
- I am not sure how successfully it isolates dependency on a platform(like Oracle or Sql Server), but it does relieve you of maintaining tons of unnecessary SPs.
- Bad C# coding is certainly not a replacement of SPs. If you are writing business logic in a highly evolved language like it, you need to make sure that you are following best coding practices and patterns. If you end up writing monolithic procedural code in C#, you need to evolve before you can understand the true benefits of this discussion.
- What about debugging? I don't think that's a big deal. You can use Sql Profiler for the parameters. Agreed that you can't have IDE like debugging but Sql Server IDE is sufficient to trace a bug. You can also unit test the output generated from the database in your application with NUnit. So if you have good coverage of the SP side code, you will get to know what all you have broken as a result of the changes you made. Neat. So, this doesn't convince me to port my business logic to C#.
- SPs can't be replaced in Data driven applications like report generators, but its advisable to code the business logic in C# for applications that do data manipulation like a Financial calculator!
You may consider reading the following articles for more details.
Dude, where's my business logic?
Stored procedures are bad, m'kay?
This one is interesting because it has people fighting for their favorite approach.
Who Needs Stored Procedures, Anyways?
My take on this: Be pragmatic. Be need driven. Be domain driven.
C#: Could be any programming language.