Stored Procedures vs. SQL Calls
This has gotten bandied around a lot in a development group I’m in, so here’s my take. There’s lots of arguments for and against stored procedures. The linked article is quite old, but the article and the comments quite well cover the issue.
Basically, the arguments against amount to: There’s no real performance boost, it moves your code out of the code layer and into the database layer and/or otherwise creates an additional tier, and it doesn’t add any security.
I use stored procedures sparingly in places where I either need to assemble a large chunk of data using multiple queries, but just want to provide a function call to the program to get the result set. (AKA: A view). I also use them when I want to hide information in the database from other developers (ex: Social security numbers or credit card info in transaction logging tables is hidden by not giving anyone select permission on the table, but giving people access to a view.
I use stored procedures in places where I’d have to write a lot of two-way communication. For instance, when creating a user in a game I’m working on, you insert the user, get the ID back, update the invite code record to show it’s been used and is now associated with an actual user, create the user’s HQ, create a warehouse and hangar in that HQ, create ships and put them in the hangar, create equipment and put it in the warehouse… That’s not just a lot of code to have down in the application level, but it’s a lot of back and forth communication as a query gets sent and then the response comes back and then more queries get generated and sent. It’s not something you can batch, even — you have to wait for the last_insert_id() to come back and get the result out of the dataset. I’ve reduced about 300 lines of application code down to a 50 line stored procedure. Nice, huh?
I also use stored procedures to abstract complex calculations — like fetching information about n-dimensional space around an object. In one case, I need to know what’s on either side, in three dimensions, out to a range of two ‘zones’ around a particular object. The code to do that is big and hairy and messy, even wrapped up as a library function. It makes sense to put it in a stored procedure because the queries are all dependent, and the application doesn’t care about anything except the resultset.
When you’re using compiled languages in a client-server program, stored procedures can save you a lot of time. Imagine a beta period where you’re frequently releasing bugfix releases. Imagine if all you had to do instead was to update a stored procedure in a database. Much nicer, ‘eh?
Definitely don’t use sprocs as your garden-variety crud. That’s not worth it… there’s better libraries for that, and using stored procedures with them just gets in the way. Look at the words I’ve put in bold above. Those are the real reasons to use stored procedures. Adding another layer to your application can be a two-edged sword. Over-use it, and you’ll end up slicing yourself to ribbons. But it is there to be used, and it is a viable choice!