5 Replies - 1051 Views - Last Post: 07 April 2015 - 05:30 PM

#1 astonecipher  Icon User is offline

  • Too busy for this
  • member icon

Reputation: 2343
  • View blog
  • Posts: 9,398
  • Joined: 03-December 12

Stored Procedure's place in programming

Posted 20 March 2015 - 10:05 AM

I had a discussion with a very experienced .Net developer the other day regarding some code. In that conversation he was utilizing stored procedures for all of the data access layer queries.

Now, we all know the importance of prepared statements, but how many of you are utilizing stored procedures in your development? Admittedly, I have rarely used them. If a change needed to be made, I would rather modify the code base, then change the database that goes along with it. May be wrong, but I am looking into utilizing them more going further as I can see a major benefit going forward.

Is This A Good Question/Topic? 0
  • +

Replies To: Stored Procedure's place in programming

#2 ArtificialSoldier  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 1840
  • View blog
  • Posts: 5,793
  • Joined: 15-January 14

Re: Stored Procedure's place in programming

Posted 20 March 2015 - 10:12 AM

I haven't used them much, the feeling I get is that the most useful environment for stored procedures is on a large project with specialist workers. You might want your programmers to only focus on application logic, and you have a dedicated team of database administrators responsible for designing and implementing the database itself. The DBAs would create the stored procedures to work with the structure they've come up with, and the programmers just use the SPs to interact with the database rather than trying to write queries themselves. That lets both the DBAs and programmers focus on what they're good at.
Was This Post Helpful? 0
  • +
  • -

#3 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 13561
  • View blog
  • Posts: 54,110
  • Joined: 12-June 08

Re: Stored Procedure's place in programming

Posted 20 March 2015 - 10:13 AM

I had a boss that was super duper against stored procedures. Everything was in the code and that wasn't bad for most things, but man.. trying to do more specialized and powerful queries sucked. I tend to shove as much - if not all - of any database interactions into a stored procedure. My theory is:
1. I like the separation.
2. I do not need to burn time copy/pasting outside of the code.
3. I know where all my DB interactions are... and no 'gotcha' for having something squirrel away in the code.
4. Power functionality.
5. Commenting
6. TFS can version control stored procedures, better, now..
7. SQL updates do not require a code release.
Was This Post Helpful? 0
  • +
  • -

#4 Dormilich  Icon User is offline

  • 痛覚残留
  • member icon

Reputation: 4138
  • View blog
  • Posts: 13,074
  • Joined: 08-June 10

Re: Stored Procedure's place in programming

Posted 20 March 2015 - 10:18 AM

View PostArtificialSoldier, on 20 March 2015 - 06:12 PM, said:

the feeling I get is that the most useful environment for stored procedures is on a large project with specialist workers. [Ö]

thatís what I have experienced as well, in one project where I had to code the PHP base, I only needed to concern myself with the correct calls, no extra SQL needed. certainly it makes swapping databases easier since it separates (for instance) PHP and the database (Facade Pattern, if Iím not too wrong).
Was This Post Helpful? 0
  • +
  • -

#5 baavgai  Icon User is online

  • Dreaming Coder
  • member icon


Reputation: 6996
  • View blog
  • Posts: 14,635
  • Joined: 16-October 07

Re: Stored Procedure's place in programming

Posted 20 March 2015 - 11:13 AM

In the bad old days of databases, users as well as developers and DBAs logged into databases and wrote SQL queries. Also, DBAs often don't trust anyone else to write efficient SQL. There is some justification for this bias: good SQL is basically functional programming and some developers do figure out how to do some pretty horrid things with SQL databases.

The solution, writing a stored procedure DAL and never touching a table directly. While this offers the warm fuzzy of isolation, it turns doing anything not already in the DAL into a request for a new stored procedure. It makes the DBA part of the development stream. Asking even the most trivial question of the data requires another stored procedure. It is, simply, a maintenance and development nightmare.

The biggest problem with the stored procedure DAL, aside from inflexibility, is portability. Stored procedures are vendor specific; they are the definition of non portable. If you want to support multiple databases, you'll have to reinvent the wheel every single time.

Now, a hybrid of this that does make a little more sense is only allowing CUD part of CRUD via stored procedures. However, it's still a lot of extra work for often dubious benefits.
Was This Post Helpful? 0
  • +
  • -

#6 e_i_pi  Icon User is offline

  • = -1
  • member icon

Reputation: 879
  • View blog
  • Posts: 1,893
  • Joined: 30-January 09

Re: Stored Procedure's place in programming

Posted 07 April 2015 - 05:30 PM

At my current workplace we use stored procedures exclusively (excepting a particular application that is decades old and can't call SPs). There's upsides and downsides to this. Upsides are code separation (mmm warm fuzzy), the stored procs can be independently tested and developed, and if multiple services / applications call the stored proc, then you get the extra warm fuzzy of DRY. The downside is that it is very difficult for a developer to test out a proposed new / altered stored proc alongside their codebase without having the DBAs run it in a dev environment for them. The ultra suck downside is that it can incur a great deal of regression testing, and it can sometimes be difficult to identify all the apps that require regression testing, especially when you services calling services calling services, and have multiple source control systems, especially if you cannot string search via your source control client.

My previous workplace had a mix of stored procs and ad hocs. I implemented stored procs across the entire BI reporting codebase, but the flagship app still used ad hocs, which (as a DBA) drove me insane, as new code releases to prod were sometimes not stress tested properly, and the whole thing fell down in a steaming heap. I wanted to get everything as stored procs so that database maintenance and upgrades were completely in the realm of the DBAs - if the devs needs to store more information or different information in the database, they could just tell the DBAs what parameters they were sending in for SELECTs, what parameters they wanted to use for CRUDS, and what format they wanted in return. This means the DBAs could work independently and change the schema of the database for optimisation purposes, with only unit tests required not end-to-end regression testing. I may have been naive, but that company was full of cowboys, and I was trying to Wyatt Earp some of it into order.

I'm all for stored procs, so it may surprise you that on my website I exclusively use ad hoc queries, with the only stored procs being those that are called by triggers. The reason for this is one that hasn't been discussed in this thread yet, and that's the database engine you're using. Not all database engines gain (what is in my opinion) the greatest benefit of stored procs, which is a global cache of execution plans. MSSQL, Oracle and DB2 have globally cached execution plans for stored procs, meaning one connection can call the stored proc and generate the execution plan, and any subsequent connections utilise that execution plan on their calls. You don't get that on PostgreSQL (unless you use pgBouncer, which I won't go into details about), nor MySQL. For the codebase on my site, it's easier for me to have all the SQL ad hoc right now - I may change my opinion in the future, but this at least gives you another point to consider.

This post has been edited by e_i_pi: 07 April 2015 - 05:33 PM

Was This Post Helpful? 2
  • +
  • -

Page 1 of 1