¿Qué es un procedimiento almacenado?

Los procedimientos almacenados son un lote de sentencias SQL que pueden ejecutarse de un par de maneras. La mayoría de los DBMs soportan procedimientos almacenados; sin embargo, no todos lo hacen. Usted necesitará verificar con la documentación de ayuda de su DBMS particular para los detalles. Como estoy más familiarizado con SQL Server, usaré ese como ejemplo.

Para crear un procedimiento almacenado la sintaxis es bastante simple:

CREATE PROCEDURE .

     <param> 

AS

Así, por ejemplo

CREATE PROCEDURE Users_GetUserInfo

    @login nvarchar(30)=null

AS

    SELECT * from [Users]
    WHERE ISNULL(@login,login)=login

Una de las ventajas de los procedimientos almacenados es que se puede centralizar la lógica de acceso a los datos en un solo lugar que luego es fácil de optimizar para el DBA. Los procedimientos almacenados también tienen un beneficio de seguridad en el sentido de que usted puede conceder derechos de ejecución a un procedimiento almacenado, pero el usuario no tendrá que tener permisos de lectura/escritura en las tablas subyacentes. Este es un buen primer paso contra la inyección de SQL.

Los procedimientos almacenados tienen sus inconvenientes, básicamente el mantenimiento asociado a su operación básica CRUD. Digamos que para cada tabla tienes un Insert, Update, Delete y al menos un select basado en la clave primaria, eso significa que cada tabla tendrá 4 procedimientos. Ahora tome una base de datos de tamaño decente de 400 tablas, ¡y tendrá 1600 procedimientos! Y eso suponiendo que no haya duplicados, lo que probablemente ocurrirá.

Aquí es donde el uso de un ORM o algún otro método para autogenerar tus operaciones CRUD básicas tiene mucho mérito.

Un procedimiento almacenado es un conjunto de sentencias SQL precompiladas que se utilizan para realizar una tarea especial.

Ejemplo: Si tengo una tabla Empleado, la cual es

Employee ID  Name       Age  Mobile
---------------------------------------
001          Sidheswar  25   9938885469
002          Pritish    32   9178542436

Primero recupero la tabla "Empleados":

Create Procedure Employee details
As
Begin
    Select * from Employee
End

Para ejecutar el procedimiento en SQL Server:

Execute   Employee details

--- (Employee details is a user defined name, give a name as you want)

Luego, en segundo lugar, estoy insertando el valor en la tabla de empleados

Create Procedure employee_insert
    (@EmployeeID int, @Name Varchar(30), @Age int, @Mobile int)
As
Begin
    Insert Into Employee
    Values (@EmployeeID, @Name, @Age, @Mobile)
End

Para ejecutar el procedimiento parametrizado en SQL Server:

Execute employee_insert 003,’xyz’,27,1234567890

  --(Parameter size must be same as declared column size)

Ejemplo: @Nombre Varchar(30)

En la tabla Employee la columna Name debe ser varchar(30).

Generalmente, un procedimiento almacenado es una "Función SQL." Tienen:

-- a name
CREATE PROCEDURE spGetPerson
-- parameters
CREATE PROCEDURE spGetPerson(@PersonID int)
-- a body
CREATE PROCEDURE spGetPerson(@PersonID int)
AS
SELECT FirstName, LastName ....
FROM People
WHERE PersonID = @PersonID

Este es un ejemplo centrado en T-SQL. Los procedimientos almacenados pueden ejecutar la mayoría de las sentencias SQL, devolver valores escalares y basados en tablas, y se consideran más seguros porque evitan los ataques de inyección SQL.