Page 1 of 2

what is a stored procedure?

Posted: Tue Dec 22, 2009 10:51 pm
by daedalus__
can someone explain to me what a stored procedure is in real plain english? i've been reading about it but i am more confused now.

Re: what is a stored procedure?

Posted: Tue Dec 22, 2009 11:39 pm
by alex.barylski
A bad practice. :P In all serious-ness. SQL is typically understood to be a declarative programming language, similar to HTML. These are occassionally refered to as DSL (Domain specific languages) because of their specialized nature. Some SQL vendors (MySQL, MSSQL, PG, etc) support stored procedures, which are basically a mini SQL based programming language that executes under the context of the RDBMS. They are considered bad practice by some, because they can often be abused and push much business logic into the RDBMS as opposed to the core programming language, in this case maybe PHP.

At the same time, using stored procs can be a very interesting solution, much like a table data gateway pattern.

A stored proc, when used correctly, acts very much like a view, although with more power. I hate SQL (much like I hate recursive programming, regex, etc) anything beyond two or more JOINs and I start crying. I struggle with wrapping my head around all that relationship resolution stuff. The last place I worked had a guy who was really good with SQL so any time I needed data I would go to the DBA and say I need this data so a few minutes later he would have a stored proc for me which I would call and it would do the rest, such as JOINs, cascading deletes, updates, etc.

It was very much like having a table data gateway or data access layer, but I invoked the method as a SQL stored proc instead of a native PHP function.

In this way it separates the responsibility of a architecture focused developer from that of a DB developer, which makes things a little easier.

Stored procs are a little faster than PHP in many cases as well. For instance, say you wanted to emulate a cascading effect (because your RDBMS doesnt support forign keys) to enforce data integrity when deleting a master record from say a users table. You might have 10 other tables which associate data with that user account. That data should be deleted as well when the user account is removed.

Instead of having to write all that cascading SQL code by hand in PHP you could achieve that in a stored proc and because its executed on the SQL server side, the engine is better at optimizing, caching your queries than say PHP, as PHP would have to send each table DELETE one at a time, unless you used prepared statements, but stored procs are still faster.

There are as many good points as there are bad points as to whether to use stored procs or not. Personally Id rather have that code in PHP because I hate working in SQL especially stored procs (which introduces yet another sntax to memorize).

Views (in case you are unaware) are similar to stored procs, but less powerful. When you have an advanced search function, that might require 5 or 6 tables to complete, itès often easier to write a view and call that view from within PHP, instead of hammering out all that code in a PHP generated query. It can occassionally lead to more reusability, again depending on how you designed your application.

Basically both stored procs and views are a way of decoupling some code from the application logic into the context of the RDBMS instead. Your really just shifting responsibilities from one layer to another and like I said both have pros and cons, you have to factor in a lot of variables before coming to a solid conclusion.

Cheers,
Alex

Re: what is a stored procedure?

Posted: Wed Dec 23, 2009 2:11 pm
by daedalus__
thanks alex they are pretty much exactly what i thought they were.

i think that whether they are a bad practice or not depends on the application. sometimes you have to do work on your database where you don't need any other language, or like you said you may need to perform many operations at once.

i really like sql myself and i'm glad to be getting back into it lately. :)

Re: what is a stored procedure?

Posted: Thu Dec 24, 2009 4:20 am
by VladSun
viewtopic.php?f=2&t=95164
:)
PCSpectra wrote:Views (in case you are unaware) are similar to stored procs, but less powerful.
I really can't agree with that statement ...

Re: what is a stored procedure?

Posted: Thu Dec 24, 2009 4:46 am
by onion2k
PCSpectra wrote:A bad practice. :P In all serious-ness.
PCSpectra wrote:A stored proc, when used correctly, acts very much like a view, although with more power. I hate SQL (much like I hate recursive programming, regex, etc) anything beyond two or more JOINs and I start crying. I struggle with wrapping my head around all that relationship resolution stuff. The last place I worked had a guy who was really good with SQL so any time I needed data I would go to the DBA and say I need this data so a few minutes later he would have a stored proc for me which I would call and it would do the rest, such as JOINs, cascading deletes, updates, etc.
Your second section highlights the most useful aspect of stored procedures. They're essentially "database functions" that should be written by someone who knows SQL and databases very well. That fact makes your initial statement very wrong.

Re: what is a stored procedure?

Posted: Thu Dec 24, 2009 1:15 pm
by alex.barylski
i think that whether they are a bad practice or not depends on the application. sometimes you have to do work on your database where you don't need any other language, or like you said you may need to perform many operations at once.
It does depend entirely on your skillset and application requirements, operating environment, etc.

If you have someone (or you yourself) who is a skilled DBA then using stored procedures can really be a blessing if you are not interested in writing SQL yourself. On the other hand, using stored procs usually results in some business logic being pushed into the stored proc and outside the core language, which has several negative consequences:

1. It's difficult if not impossible to unit test the business logic inside a stored proc
2. If your DBA disappears you and your team are fubar'ed (it's far easier to find PHP developers than DBA's)
3. Business logic is now in a layer other than PHP so logic isn't immediately obvious to PHp developers (spend a great deal of time as a new developer trying to figure out how or where something happened only to later learn it's done in a stored proc)
4. Validations or business logic inside the stored proc needs to somehow communicate those errors to PHP which then needs to bubble up to the controller layer for redirection or handling, etc. I have yet to see an elegant solution. You might check return codes but exceptions are much cleaner.
5. They introduce another possible dependency on a RDBMS so porting from one (MSSQL to MySQL or SQLite, etc) can become impossible.

The #1 and #4 are most critical for me. So long as you can keep the business logic *out* of the stored proc (which DBA's will fight tooth and nail as they want more control over what is going on and usually don't understand best practices and good design at the architectural level) and just do SQL stuff stored procs are a blessing in disguise.

I personally tend to stay away from stored procs simply because I hate working in SQL but if I had a experienced SQL guru on hand and they were willing to let go of some control and listen to me (for the sake of unit testing, portability and error handling) I would be all for stored procs. They do have benefits but whether they make sense depends on many variables.
I really can't agree with that statement ...
Sorry man I'm not sure I follow. I read through the previous thread quickly...what is it about views that I am missing? As I understand views are really only good for compartmentalizing complex SELECTS so instead of having to query 10 tables I could throw that code into a View and the View then acts as a virtual table reflecting all real tables involved???

It's handy to use Views for complex queries which cannot be expressed in either OO SQL or a simple data mapper, etc. I've also become a fan of keeping reports in Views and sometimes stored procs.

Still I see views as a less flexible (if not powerful) version of stored procs the obvious difference being Views are virtual tables whereas stored procs are procedures.
They're essentially "database functions" that should be written by someone who knows SQL and databases very well. That fact makes your initial statement very wrong.
Yes and no. You can argue until were blue in the face as to whether stored procs are bad practice. If you use them with a great deal of discipline then sure they are awesome. Much like you can use GOTO or globals very sparingly in rare occassions to solve a problem more elegantly. Most will still agree that in general GOTO and globals are bad practice because when they are abused (which is often the case with stored procs for the reasons I stated above) they result in really bad code.

So I wasn't nessecatily saying stored procs are the work of the devil (hence the :P after I said it) in general they should be avoided unless you have very clear set of usage policies in place to basically dictate their use, when, where, why, how, etc.

I am extremely anal about every line of code in my software. If something goes wrong I can tell you (without looking at the source) exactly what file (and usually function) to look in within seconds of seeing the error and diagnosing the problem. When code starts ending up in stored procedures and validations are in templates and adhoc programming is an accepted practice, this ability flies out the window, along with company money.

That was really the only point I was trying make. As long as everyone understands (and agree`s) exactly what should go in a stored proc and that stays consistent throughout the project I would be fine with using stored procs.

Cheers,
Alex

Re: what is a stored procedure?

Posted: Thu Dec 24, 2009 3:58 pm
by VladSun
Views are (or may be):
- result of a single query stored server side;
- defined as a single query that is a READ query;
- to be handled as (virtual) tables;
- writable!

Stored procedures are (or may be)
- multiple queries stored server side;
- defined as a set of queries that can perform READ/WRITE operations;
- to be handled as tables (i.e. set of rows), or as a scalar value, or NO VALUE;
- not writable!;

- defined by using PL language;
- have input parameters;
- have output parameters (i.e. "arguments passed by address");
- can be used as agregators;

Re: what is a stored procedure?

Posted: Thu Dec 24, 2009 10:16 pm
by alex.barylski
What do you mean by writable?

My understanding is that a view would never change data only select it and manage it, whereas stored procs can and often do manipulate data? Re-quoting your quote of myself (tongue twister?):
pcspectra wrote:Views (in case you are unaware) are similar to stored procs, but less powerful.
VladSun wrote:I really can't agree with that statement ...
What is it about that statement you don't agree with? From the list you have provided, does it not make sense to say Views are less powerful or perhaps flexible or capable would have been the better term?

Unless you mean my saying "similar but less powerful" in which case, I only meant that, views and stored procs are similar in the sense they let you shift responsibility from the core language into the realm of SQL, nothing more. I certainly didn't mean to imply that stored procs and views are technically equal or similar like a function versus procedure in some langauges.

Cheers,
Alex

Re: what is a stored procedure?

Posted: Fri Dec 25, 2009 1:26 am
by daedalus__
these are ridiculously useful. i could see myself wanting to use stored procedures for data that gets fed to multiple applications and/or languages. oh and about unit testing sql:

Code: Select all

Based on the realization that the stored procedure author need not be the same person who writes the Java code, and may not even know Java or JUnit yada yada


fun

Re: what is a stored procedure?

Posted: Fri Dec 25, 2009 2:54 am
by VladSun
PCSpectra wrote:I certainly didn't mean to imply that stored procs and views are technically equal or similar like a function versus procedure in some langauges.
It's more like comparing arrays and functions - that's why I don't agree with you.

http://dev.mysql.com/doc/refman/5.0/en/create-view.html
Some views are updatable. That is, you can use them in statements such as UPDATE, DELETE, or INSERT to update the contents of the underlying table. For a view to be updatable, there must be a one-to-one relationship between the rows in the view and the rows in the underlying table. There are also certain other constructs that make a view nonupdatable.

Re: what is a stored procedure?

Posted: Fri Dec 25, 2009 5:56 am
by VladSun
I would use an SP when:
- DB design is delegated to a DBA because of the complexity of the DB design, optimization and data integrity;
- there are more than one applications (and maybe a developers teams) using the same DB;

Also, I would always use foreign key constraints, triggers etc. in order to keep the data integrity at DB level.

Re: what is a stored procedure?

Posted: Fri Dec 25, 2009 8:01 am
by Eran
there are more than one applications (and maybe a developers teams) using the same DB
In such a case I would usually expose an API to the database instead of allowing direct access. Depending on the level of cohesion between the applications and the database.

Re: what is a stored procedure?

Posted: Fri Dec 25, 2009 1:01 pm
by alex.barylski
- there are more than one applications (and maybe a developers teams) using the same DB;
That is a good argument but in that case I think I would provide a web services API that Windows desktop applications and/or web applications would use.

Personal preference, obviously using a web services layer is going to be slower than direct access, I prefer abstraction over performance, so depending on what side of the fence your on, that will be a big factor in your decision making.

Cheers,
Alex

Re: what is a stored procedure?

Posted: Fri Dec 25, 2009 3:16 pm
by VladSun
pytrin wrote:
there are more than one applications (and maybe a developers teams) using the same DB
In such a case I would usually expose an API to the database instead of allowing direct access. Depending on the level of cohesion between the applications and the database.

What kind of API exactly?
E.g. you have a C# desktop application, an Asp .Net web site and a PHP Intranet web application (real case of mine). What do you suggest? Which platform will be the "winner"? How should I convince the other developer teams the it should be done somehow...?

Re: what is a stored procedure?

Posted: Fri Dec 25, 2009 3:19 pm
by VladSun
PCSpectra wrote:Personal preference, obviously using a web services layer is going to be slower than direct access, I prefer abstraction over performance, so depending on what side of the fence your on, that will be a big factor in your decision making.
An SP is an abstraction in this case, IMHO ...