Modularización de consultas por medio de procedimientos almacenados en SQL Server

En SQL Server un procedimiento almacenado es un grupo de una o más instrucciones de Transact-SQL (Lenguaje de programación). Estos procedimientos aceptan parámetros de entrada y devuelven múltiples valores en forma de parámetros de salida al programa que llama al procedimiento, además contienen instrucciones de programación que realizan operaciones en la base de datos. También, estos procedimientos devuelven un valor de estado al programa que llamo al procedimiento para indicar éxito o fracaso (y el motivo del fracaso).

Gracias al control de flujo que ofrece SQL Server es que podemos usar los procedimientos almacenados. Como se describió, Transact-SQL tiene diversas instrucciones que nos permiten emplear estructuras de control tal como en los lenguajes de programación, es decir, podemos usar construcciones del lenguaje para modificar el flujo de ejecución de las instrucciones de un programa.

Los procedimientos almacenados tienen la ventaja de que permiten modularizar soluciones de software al encapsular el código en unidades manejables y con ello facilitar su reutilización. Además, con el uso de procedimientos almacenados los comandos de estos se ejecutan como un único lote de código, esto puede reducir significativamente el tráfico de red entre el servidor y el cliente, ya que solo se envía la llamada a través de la red para ejecutar un procedimiento determinado. Sin la encapsulación de código que proporciona un procedimiento, cada línea de código tendría que atravesar la red.

Creación de procedimientos almacenados

La sentencia CREATE PROCEDURE es la que nos permite la creación y persistencia de bloques de código, además, cuando se usa esta sentencia, el procedimiento se guarda en la carpeta Databases > NombreBaseDeDatos > Programmability > Stored procedures, aquí es donde podemos encontrar todos los bloques de código que se han guardado en la propia base de datos. La carpeta de procedimientos almacenados se muestra en la siguiente imagen.

Como ejemplo de código, vamos a suponer que tenemos una tabla de productos que tiene un campo cantidad y que deseamos actualizar los valores de este campo, para esto, podemos crear un procedimiento almacenado que nos va permitir llamarlo cada vez que lo necesitemos sin tener que volver a escribir el código en su interior. El código es el que se muestra en seguida.

CREATE PROCEDURE p_actualiza_stock

AS

BEGIN

    IF EXISTS(SELECT * FROM productos

            WHERE cantidad = 0)

        UPDATE 

            productos SET cantidad = 20

        WHERE 

            cantidad <= 5;

END

Con el código anterior se guarda el procedimiento y ahora solo falta ejecutarlo por medio de la sentencia EXEC, esto se muestra a continuación.

EXECUTE p_actualiza_stock;

Es importante mencionar que cada vez que un procedimiento se guarda y modifica en la base de datos, será necesario ejecutarlo o mandarlo a llamar por medio de la sentencia EXEC.

Actualización de procedimientos almacenados

Para actualizar o modificar un procedimiento que ya guardamos previamente, tenemos que usar la sentencia ALTER PROCEDURE. Como ejemplo, podemos modificar el procedimiento que describimos previamente, en este caso podemos agregar 10 unidades más a cada artículo que tenga 20 elementos, entonces el código es el siguiente.

ALTER PROCEDURE p_actualiza_stock

AS

BEGIN

    IF EXISTS(SELECT * FROM productos

            WHERE cantidad = 10)

        UPDATE 

            productos SET cantidad = 30

        WHERE 

            cantidad = 20;

END

De igual manera, la instrucción ALTER modifica y guarda el procedimiento en la base de datos en cuestión.

Borrar procedimientos almacenados

Para borrar procedimientos almacenados tenemos que usar la instrucción DROP PROCEDURE, entonces para borrar el procedimiento con el que hemos estado trabajando tenemos que usar el código que muestra a continuación.

DROP PROCEDURE p_actualiza_stock;

Con esto, el módulo de actualización de stock ya no estará disponible en la carpeta Stored Procedures de la base de datos.

Parámetros de entrada en procedimientos almacenados

Para usar parámetros o variables al interior de una procedimiento almacenado tenemos que declarar las variables antes del propio código de ejecución, es decir, antes de la clausula AS, recordemos que la declaración de variables tiene la sintaxis @nombreVar TipoDeDato = valor, además, si se usan varias variables tenemos que separarlas por comas. En el siguiente ejemplo se muestra un procedimiento almacenado que nos permite buscar un producto con determinados valores en cada campo. En este caso la tabla de productos tiene los campos nombre, precio y cantidad (existencia) en su estructura.

CREATE OR ALTER PROCEDURE p_buscar_producto

@producto VARCHAR(40) = 'Microcontrolador',

@precioProducto SMALLMONEY = 29.99,

@existencia INT = 35

AS

SELECT * FROM productos

WHERE

    nombre = @producto AND

    precio = @precioProducto AND

    cantidad = @existencia;

En esta ocasión se uso la palabra reservada ALTER en el encabezado del procedimiento almacenado, con esto podemos crear y/o modificar un procedimiento en el mismo lugar sin la necesidad de realizar estas operaciones por separado.

Referencias

En los siguientes enlaces se puede obtener más detalles técnicos sobre las sentencias de código usadas en este artículo.

• Control de flujo en SQL Server
• Sentencia CREATE PROCEDURE
  
• Sentencia EXECUTE
• Sentencia ALTER PROCEDURE
• Declaración de variables T-SQL