Definición básica:
Los store procedures (SP) son scripts con secuencias de sentencias SQL que permiten realizar una serie prefijada de procesos para obtener un resultado determinado, el que puede ser la obtención de valores o la manipulación de tablas.
Con scripts tienen la diferencia de ser parametrizables, por lo que no solamente se les puede indicar con qué valores se trabajará, sino también qué tipo de valores se podrían obtener de ellos.
Como son secuencias, en definitiva son "programas" realizados en SQL y se cuenta con cierto tipo de sentencias que solamente pueden operar dentro de ellos, tales como los controladores de flujo IF/THEN/ELSE END IF; CASE/WHEN ... THEN/END CASE, REPEAT /LEAVE END REPEAT y otros más.
También por medio de ellos se puede realizar el barrido registro a registro de una tabla, para lo cual se definen elementos de tipo CURSOR, y HANDLERS de proceso de tablas.
Pero uno de los elementos más importantes es que a través de ellos, en las tablas InnoDB, se puede acceder a la implementación de TRANSACTIONS y recuperación de problemas en las transacciones.
Finalmente, y no menos importante, es una de las mejores formas de evitar que la base sea sometida a ataques por
SQL injection, ya que al sólo permitir el ingreso de parámetros, si el contenido de los parámetros no se ajusta a los valores esperados no puede ejecutarse. En vez de ello, si lo que la base espera es el envío de una sentencia (las query que usualmente usas en PHP), y la conexión es hakeada, ¿te imaginas el tipo de problemas que se puede producir?
Los SP no existen como sentencias, sino que son creados por un script y pertenecen a UNA base de datos. Una vez que se lo crea, sigue existiendo mientras no se lo borre o modifique.
Se lo convoca con una sentencia "CALL NombreSP(param1, param2, param3, ...paramN)". Puede hacerse que devuelva un valor en la misma variable, o bien que devuelva un resultado como tampo de una tabla o una tabla, al igual que un SELECT común y silvestre. Cuando usas programación en .NET puedes usar un conector propio de MySQL, el que tiene un control más avanzado sobre las parametrizaciones de las sentencias de SQL.
Yo habitualmente uso los SP cuando debo realizar una serie compleja de pasos que requeriría varios llamados a la base con diferentes sentencias; de esa forma, lo llamo una sola vez y que el SP se encargue del resto.
tengo SP que tienen una media docena de líneas de código, y SP que tienen más de 500. Hay uno de ellos al que envío tres parámetros y con ellos realiza un script de actualizacion a una tabla según los datos que compila a través de un SELECT con 8 subconsultas anidadas, otras unidas y algunos cuantos JOIN con uso de funciones almacenadas y funciones de MySQL. Todo el proceso me insumiría a través de sentencias sucesivas al menos un minuto y medio. Descargarle esa tarea a la base, hace que todo el proceso, en el peor escenario, dure alrededor de 15 segundos.