[MySQL]: Stored Procedure's and select statements

Question

I'm looking into stored procedures at the moment.

According to this article (page 8) in the dev section of the mysql website...

Ordinarily, it's not normal to put SELECT statements in stored procedures, this is for illustration. I decided that some procedure should simply select from our table, so that when you call the procedure it will be obvious that it's working.

Why is that?

Is using Stored-procedures to simplify complicated select statements not 'best-practices'?

What are some specific situations where it is beneficial to use a stored procedure? Example?

Answer 1

Generally stored procedures are intended for complex processing in the database. There are debates raging about their benefits. I never saw that SELECTs in a stored procedure was a bad thing but I wouldn't expect that every single SQL statement that has to be written goes into a stored procedure either. It should be reserved for those processing that involve multiple statements and will have to be performed repeatedly.

Jeff has a rant about them here.

To answer your direct question for specific examples, I have found that I avoid them because of the portability issue. I attempt to do all my processing application side. At the same time I do not have to worry about network bandwidth in my application so each situation is different.

Answer 2

A specific situation where it is beneficial to use Stored Procedure/Routines is that it can provide error checking on parameters similar to functions in OO paradigm. It gives added 'Encapsulation'

A simple example:

CREATE PROCEDURE select_table(IN @id INT)
BEGIN
  IF @id < O THEN
    -- ERROR!  do something here
  ELSEIF
    SELECT * from TABLE WHERE id = @id;
  END IF
END