SQL For Programmers – Stored Procedures (Better than LINQ)

arct-06 [Don't start the flames until you read the entire post :). ]

While we could go on with the various syntax elements in a SELECT statement, I doubt that it would be as helpful as the items we’ve already covered.  Most of what I use on a day to day basis is some combination of what I’ve shown already.

Where SQL really starts getting fun though, is with stored procedures.

But why use Stored Procedures at all?

The historic answer to this question has always been, “speed.”  Every time you send a query statement to the SQL server from your application, it has to re-compile it.  There are ways to compensate for this, but the code you’d need to write to do this is almost harder than just writing the stored procedure to begin with.

However, now that we have LINQ, it is really hard to make the performance argument any more.  In fact, the argument of the day is, “.NET Programmers don’t need to learn SQL, they should learn LINQ instead.”  And, if all you ever write is a basic SELECT, INSERT, UPDATE, or DELETE statement, you’d be right.  LINQ has all that optimization code built into it, so you won’t get any significant performance gains by moving to a stored procedure.

However, there are two reasons why I continue to argue for Stored Procedures over LINQ.

  • LINQ does not currently support all of the SQL syntax.  For example, there is no clean way to use the IN keyword with a Sub-Select like we discussed a couple of days ago in Finding IN a List.
  • There is no clean way of having LINQ process multiple tables and return just the data we are looking for.  Instead, you’d need to pull the data from the multiple tables, process the data in .NET and throw out the data you don’t need once you have your result.While that method works, it is sending more data “over the wire” than is necessary and is clearly a performance issue.

In short, the reason you want to use stored procedures is because you gain flexibility you would not otherwise have.

In the same way that using a three layer architecture gives you the flexibility and control you will need in your applications, placing your data retrieval in a stored procedure allows you to do things with SQL that you would not otherwise be able to do using LINQ.

This is not to say that I’m anti LINQ.  It definitely allows me to do things in .NET that would otherwise require many more lines of code.  I just don’t think it is the right tool for the job when it comes to retrieving data from your SQL database.

So, next week, we’ll start looking at the syntax for stored procedures and some of the things we can do in a stored procedure other than just wrapping our SELECT, INSERT, UPDATE, and DELETE statements.

Related Post

  • Temporary Tables – SQL For ProgrammersTemporary Tables – SQL For Programmers You may think that you don't need a temporary table.  But if you've ever retrieved data from your database or retrieved data from a table and put it in a list of some sort simply to process it fur...
  • SQL For Developers – 9 Reasons to botherSQL For Developers – 9 Reasons to bother If you are a developer like I am, you've probably gotten by with pretty simple SQL for quite a while. In fact, my experience has been that developers don't get much past SELECT, INSERT, UPDATE, an...
  • SQL For Programmers – Stored Procedure BasicsSQL For Programmers – Stored Procedure Basics Now that we've established a reason for using them, let's continue with the basic syntax of the stored procedure. I think you'll find that it is not that dissimilar to writing a procedure in any o...
  • SQL IF/WHILE Blocks – SQL For ProgrammersSQL IF/WHILE Blocks – SQL For Programmers OK, all you programmer types.  Here's where SQL starts getting interesting.  You have the basic concepts of declaring variables, setting up stored procedures, and doing a basic insert, update or d...
  • SQL CURSOR – SQL For ProgrammersSQL CURSOR – SQL For Programmers One of the things it took me a while to figure out was how to get a stored procedure to loop through data in a table and pull out specific data I needed. There are actually two parts to this qu...