Procedimientos almacenados en Transact SQL

Los procedimientos almacenados son conjuntos de una o varias instrucciones almacenadas en base de datos, que pueden ser llamados desde otra parte de la aplicación. Los procedimientos almacenados permiten recibir parámetros de entrada y/o salida, aunque no siempre es necesario que devuelvan información. Además, y a diferencia de las funciones, pueden realizar operaciones internas que alteren la base de datos tales como INSERTS, UPDATES y/o DELETES.

Características y tipos

• Permiten recibir parámetros de entrada y/o salida.

• Pueden realizar operaciones internas que alteren el estado de la base de datos (INSERT INTO…)

• Pueden llamar a otros procedimientos almacenados.

• Al encapsular bloques de código, mejoran el tráfico de red entre cliente y servidor. Además, mejora la eficiencia a estimular la reutilización de código.

• Aportan mayor seguridad. Por ejemplo, los procedimientos almacenados pueden cifrarse y el uso de parámetros protege contra inyecciones directas de código SQL.

• Mejoran el rendimiento y facilitan el mantenimiento

Atendiendo a los tipos de procedimientos podemos dividirlos en:

• Procedimientos definidos por el usuario: Aquellos que el usuario define libremente y personaliza de acuerdo a su necesidades y lógica de negocio.

• Procedimientos temporales: Aquellos definidos por el usuario, pero almacenados en tempbd. Son eliminados después de la sesión en que son utilizados.

• Procedimientos del sistema: Aquellos que vienen por defecto en SQL Server.

• Procedimientos definidos por el usuario extendidos: Permiten crear rutinas externas en un lenguaje de programación como C. Este tipo de procedimientos, serán eliminados en versiones futuras de SQL Server por lo que desaconsejamos su uso.

PROCEDURE

CREATE PROCEDURE

CREATE PROCEDURE es la sentencia para crear nuevos procedimientos almacenados definidos por el usuario. La sintaxis básica es la siguiente:

CREATE PROCEDURE (parametros IN/OUT)
AS
BEGIN
    [codigo]
END

Dicho esto, vamos a utilizar como ejemplo un nuevo procedimiento que permita añadir y/o modificar las cuentas de los usuarios almacenados en la tabla Clientes:

CREATE PROCEDURE insertarModificarCuenta
(
    @id    INT,
    @cuenta INT
)
AS
BEGIN
    UPDATE Clientes
    SET cuenta = @cuenta
     WHERE idClientes = @id
END

El nuevo procedimiento: insertarModificarCuenta recibe dos parámetros de entrada (@id, @cuenta) que utiliza respectivamente para filtrar el cliente y actualizar el valor de la cuenta.

El siguiente ejemplo, tiene la finalidad de ilustrar de la manera más sencilla y esquemática el funcionamiento de un procedimiento almacenado. Sin embargo, cabe destacar, que es recomendable el uso de estructuras de control y gestión de errores en el trabajo con procedimientos. De igual manera y si tenemos una secuencia de instrucciones, puede ser útil el manejo de transacciones para no provocar cambios en base de datos si ocurre algún error durante la ejecución. Desde las siguientes secciones: GESTION DE ERRORES y TRANSACCIONES encontraréis más información al respecto.

EXEC

La instrucción EXEC permite ejecutar procedimientos. Existen dos formas rápidas de ejecutar un procedimiento almacenado:

EXEC insertarModificarCuenta @id  = 1, @cuenta = 111111111

o podemos declarar variables previamente:

DECLARE @RC int
DECLARE @id int
DECLARE @cuenta int

EXECUTE @RC = [dbo].[insertarModificarCuenta]
   1
  ,111111111
GO

En ambos casos si consultamos la cuenta del cliente con idClientes = 1, veremos que se ha actualizado correctamente el valor de la misma:

SELECT cuenta
FROM Clientes
WHERE idClientes = 1;

ALTER PROCEDURE

ALTER PROCEDURE es la instrucción que permite modificar procedimientos almacenados ya declarados por el usuario. Las modificaciones pueden ser diversas: nombre del procedimiento, número y tipos de argumentos, lógica interna…

En el siguiente ejemplo vamos a modificar nuestro procedimiento: insertarModificarCuenta para que devuelva en un parámetro de salida, un mensaje indicando los valores de la cuenta antes y después de ser modificada.

ALTER PROCEDURE insertarModificarCuenta
(
    @id    INT,
    @cuenta INT,
    @resultadoCuenta VARCHAR(255) OUTPUT
)
AS
BEGIN
    DECLARE @cuentaPrevia INT;
    DECLARE @cuentaNueva INT;
    SET @cuentaPrevia = (SELECT cuenta
                        FROM Clientes
                        WHERE idClientes = @id);
    UPDATE Clientes
    SET cuenta = @cuenta
    WHERE idClientes = @id
    SET @cuentaNueva = (SELECT cuenta
                        FROM Clientes
                        WHERE idClientes = @id);
    SET @resultadoCuenta = 'La cuenta anterior: ' +
                            CAST (@cuentaPrevia AS VARCHAR(20)) +
                           ' ha sido modificada a: '+
                            CAST (@cuentaNueva AS VARCHAR (20));
   SELECT @resultadoCuenta AS resultadoCuenta
END
GO

Las modificaciones realizadas son las siguientes:

• Añadimos un nuevo parámetro de salida: @resultadoCuenta

• Declaramos dos nuevas variables internas: @cuentaPrevia y @cuentaNueva que guardarán el valor de la cuenta antes y después de realizar el UPDATE

• Rellenamos @resultadoCuenta con un mensaje mostrando los valores de @cuentaPrevia y @cuentaNueva

• Devolvemos el valor de @resultadoCuenta en un SELECT

Procedemos a ejecutar nuevamente el procedimiento tomando en cuenta el nuevo parámetro de salida:

EXEC insertarModificarCuenta @id  = 1, @cuenta = 222222222, @resultadoCuenta = null;

Vemos que devuelve como salida:

DROP PROCEDURE

La sentencia DROP PROCEDURE permite eliminar completamente procedimientos almacenados generados dentro de SQL Server.

DROP PROCEDURE IF EXISTS insertarModificarCuenta;

Como en otros tipos de DROP, es recomendable utilizar siempre la cláusula IF EXISTS