Procedimiento almacenado SQL: Automatizar y optimizar consultas

Los procedimientos almacenados SQL son conjuntos de sentencias SQL guardadas y almacenadas en una base de datos. Pueden ejecutarse bajo demanda para realizar tareas de manipulación y validación de datos, reduciendo la necesidad de escribir código SQL repetitivo para operaciones comunes. Los procedimientos almacenados son útiles en la gestión de bases de datos porque promueven la eficacia y la reutilización. Además, permiten mejorar la seguridad y la mantenibilidad de las bases de datos. En este artículo, hablaremos de cómo crear y ejecutar procedimientos almacenados SQL, casos de uso comunes y mejores prácticas.

Para empezar, recomiendo encarecidamente seguir los cursos Introducción a SQL y Aprende SQL de DataCamp para adquirir los conocimientos básicos sobre la extracción y el análisis de datos mediante SQL. Además, la Hoja de trucos de SQL Básico, que puedes descargar, es una referencia útil porque contiene todas las funciones SQL más comunes.

¿Qué es un procedimiento almacenado en SQL?

Un procedimiento almacenado en SQL es una colección de sentencias SQL guardadas y almacenadas dentro de la base de datos. La finalidad del procedimiento almacenado SQL es realizar una secuencia de operaciones en una base de datos, como consultar, insertar, actualizar o eliminar datos.

A diferencia de las consultas SQL normales, que se ejecutan como comandos independientes, los procedimientos almacenados encapsulan un conjunto de sentencias SQL, lo que facilita la reutilización del código sin tener que escribir comandos SQL repetidamente.

Entre las ventajas de los procedimientos almacenados de SQL se incluyen las siguientes:

• Reutilización del código: Una vez creado un procedimiento almacenado, se puede llamar tantas veces como sea necesario, eliminando la redundancia en el código SQL.
• Rendimiento mejorado: Los procedimientos almacenados suelen ejecutarse más rápido porque están precompilados y almacenados en el servidor de la base de datos, lo que reduce la latencia de la red y el tiempo de compilación.
• Seguridad: Los procedimientos almacenados pueden mejorar la seguridad de los datos y el control del acceso a datos sensibles, concediendo a los usuarios permiso para ejecutar un procedimiento almacenado sin acceso directo a las tablas.

Sintaxis y estructura básicas

La sintaxis para crear un procedimiento almacenado puede variar ligeramente en función del sistema de base de datos (por ejemplo, MySQL, SQL Server, Oracle). A continuación se muestra un ejemplo general utilizando la sintaxis de SQL Server:

-- Create a stored procedure named ProcedureName
CREATE PROCEDURE ProcedureName 
    @Parameter1 INT, 
    @Parameter2 VARCHAR(50)  
AS
BEGIN
    -- SQL statements go here
    SELECT * FROM TableName WHERE Column1 = @Parameter1 AND Column2 = @Parameter2;
END;

En la sintaxis anterior;

• CREAR PROCEDIMIENTO: Este comando se utiliza para definir un nuevo procedimiento almacenado.

• NombreDelProcedimiento: El nombre dado al procedimiento almacenado. Debe ser único dentro de la base de datos.

• @Parameter1, @Parameter2: Los parámetros son opcionales; permiten que el procedimiento reciba entradas de datos. Cada parámetro se define con un símbolo @ y un tipo de datos (por ejemplo, INT, VARCHAR(50)).

• COMO INICIO...FIN: Las sentencias SQL dentro de BEGIN y END forman el cuerpo del procedimiento, donde se ejecuta la lógica principal. En este ejemplo, el procedimiento recupera registros de una tabla basándose en condiciones específicas.

Parámetros de entrada y salida

Los parámetros de entrada y salida te permiten pasar valores a y desde un procedimiento almacenado.

Por ejemplo, si @Parameter1 se define como parámetro de entrada, se le puede asignar cualquier valor cuando se llame al procedimiento, afectando a la lógica SQL o a la salida. En el ejemplo siguiente, el parámetro @UserID recupera datos específicos del UserID proporcionado.

-- Create a procedure to retrieve data for a specific user by UserID
CREATE PROCEDURE GetUserData 
 -- Input parameter: ID of the user to retrieve
    @UserID INT 
AS
BEGIN
    -- Select all columns from Users where UserID matches the input parameter
    SELECT * FROM Users WHERE UserID = @UserID;
END;

Los parámetros de salida, definidos por la palabra clave OUTPUT, permiten a un procedimiento almacenado enviar un valor de vuelta al entorno de llamada. Por ejemplo, si un procedimiento calcula un descuento, podría utilizar un parámetro de salida para devolverlo al programa que lo llama.

-- Create a procedure to calculate the discounted price
CREATE PROCEDURE CalculateDiscount 
    @Price DECIMAL(10, 2), 
    @DiscountRate DECIMAL(5, 2),  
    @FinalPrice DECIMAL(10, 2) OUTPUT -- Output: final price after discount
AS
BEGIN
    -- Calculate final price by applying the discount rate to the original price
    SET @FinalPrice = @Price * (1 - @DiscountRate);
END;

Para llamar a este procedimiento, utilizarías

-- Declare a variable to store the final price after discount
DECLARE @FinalPrice DECIMAL(10, 2);
-- Execute the CalculateDiscount procedure with a price of 100 and a 10% discount 
-- Store the output in the @FinalPrice variable
EXEC CalculateDiscount @Price = 100, @DiscountRate = 0.1, @FinalPrice = @FinalPrice OUTPUT;
-- Select and display the final discounted price
SELECT @FinalPrice AS FinalPrice;

Procedimientos almacenados en MySQL

Como ya he mencionado, la ejecución de un procedimiento almacenado en SQL puede hacerse de distintas formas, dependiendo del sistema de base de datos y de las herramientas utilizadas.

Crear procedimientos almacenados en MySQL

Crear un procedimiento almacenado en MySQL implica definir el nombre del procedimiento, los parámetros y las sentencias SQL que componen su cuerpo. El siguiente ejemplo crea un procedimiento llamado GetEmployeeDetails que toma EmployeeID como parámetro de entrada y recupera los detalles de ese empleado en concreto.

DELIMITER $
-- Create a procedure to retrieve details for a specific employee by EmployeeID
CREATE PROCEDURE GetEmployeeDetails(IN EmployeeID INT)  
BEGIN  
    -- Select all columns from Employees where EmployeeID matches the input parameter  
    SELECT * FROM Employees WHERE EmployeeID = EmployeeID;  
END$
DELIMITER ;

Ejecutar procedimientos almacenados en MySQL

La forma más habitual de ejecutar un procedimiento almacenado es mediante comandos SQL. En MySQL, utilizamos el comando CALL para ejecutar el procedimiento almacenado.

CALL ProcedureName();

Utilizando el procedimiento GetEmployeeDetails definido, la consulta de ejecución tendría el siguiente aspecto:

-- Execute the stored procedure to retrieve details for EmployeeID 101
CALL GetEmployeeDetails(101);

Procedimientos almacenados en SQL Server

SQL Server proporciona sintaxis y comandos específicos para crear, ejecutar y gestionar procedimientos almacenados. Este enfoque facilita la creación de rutinas SQL eficientes y reutilizables que pueden manejar tareas complejas con una repetición mínima.

Crear procedimientos almacenados en SQL Server

Crear un procedimiento almacenado en SQL Server implica definir el nombre del procedimiento, los parámetros y las sentencias SQL que componen su cuerpo. El siguiente ejemplo crea un procedimiento llamado GetEmployeeDetails que toma @EmployeeID como parámetro de entrada y recupera los detalles de ese empleado en concreto.

-- Create a procedure to retrieve details for a specific employee by EmployeeID
CREATE PROCEDURE GetEmployeeDetails
    @EmployeeID INT          -- Input parameter: ID of the employee to retrieve
AS
BEGIN
    -- Select all columns from Employees where EmployeeID matches the input parameter
    SELECT * FROM Employees WHERE EmployeeID = @EmployeeID;
END;

Ejecutar procedimientos almacenados en SQL Server

En SQL Server, el comando EXEC o EXECUTE llama a un procedimiento almacenado. El siguiente ejemplo muestra cómo ejecutar el procedimiento almacenado GetEmployeeDetails utilizando parámetros de entrada específicos. 

-- Execute the GetEmployeeDetails procedure with the EmployeeIDset to 102
EXEC GetEmployeeDetails @EmployeeID = 102;

También puedes ejecutar los parámetros de salida declarando la variable en el comando. En el siguiente ejemplo, @TotalSales se declara como variable para recibir la salida de CalculateTotalSales. 

-- Declare a variable to store the total sales amount
DECLARE @TotalSales DECIMAL(10, 2);
-- Execute CalculateTotalSales for SalespersonID 5, store the result in @TotalSales
EXEC CalculateTotalSales @SalespersonID = 5, @TotalSales = @TotalSales OUTPUT;
-- Display the total sales amount
SELECT @TotalSales AS TotalSales;

Te recomiendo que sigas nuestro curso Introducción a SQL Server para comprender las distintas funcionalidades de SQL Server para consultar datos. Considera también nuestra carrera completa de Desarrollador de SQL Server, que no sólo te dotará de los conocimientos necesarios para crear, actualizar y ejecutar procedimientos almacenados, sino que también te ayudará con las funciones agregadas, la unión, inserción y eliminación de tablas, y mucho más.

Usos comunes de los procedimientos almacenados

Los procedimientos almacenados SQL son útiles en situaciones en las que se requieren tareas complejas repetitivas. Las siguientes son aplicaciones reales de los procedimientos almacenados en la gestión de datos y las operaciones empresariales. 

Validación de datos y aplicación de la integridad

Los procedimientos almacenados pueden utilizarse para validar los datos antes de actualizarlos o insertarlos. En el ejemplo siguiente, un procedimiento almacenado comprueba que el correo electrónico de un cliente es único antes de insertar un nuevo registro en la tabla Customers, lo que garantiza la coherencia de los datos. Esto centraliza la lógica de validación en la base de datos, reduciendo la redundancia y garantizando una aplicación uniforme en las distintas aplicaciones.

-- Create a procedure to add a new customer, checking for duplicate email
CREATE PROCEDURE AddCustomer
    @CustomerName VARCHAR(50), 
    @CustomerEmail VARCHAR(50)
AS
BEGIN
    -- Check if the email already exists in the Customers table
    IF EXISTS (SELECT 1 FROM Customers WHERE Email = @CustomerEmail)
        -- Throw an error if the email is already in use
        THROW 50000, 'Email already exists.', 1;
    ELSE
        -- Insert new customer details if email is unique
        INSERT INTO Customers (Name, Email) VALUES (@CustomerName, @CustomerEmail);
END;

Procesamiento de datos e informes automatizados

También puedes utilizar procedimientos almacenados para generar informes periódicos o procesar grandes conjuntos de datos. Por ejemplo, un procedimiento almacenado podría agregar datos de ventas diarias de una plataforma de comercio electrónico y almacenarlos en una tabla de informes, facilitando a los equipos el acceso a las perspectivas de ventas sin ejecutar consultas complejas.

-- Create a procedure to generate a daily sales report
CREATE PROCEDURE GenerateDailySalesReport
AS
BEGIN
    -- Insert today's date and total sales into the SalesReport table
    INSERT INTO SalesReport (ReportDate, TotalSales)
    
    -- Select current date and sum of sales for today from Sales table
    SELECT CAST(GETDATE() AS DATE), SUM(SalesAmount) 
    FROM Sales 
    WHERE SaleDate = CAST(GETDATE() AS DATE);
END;

Gestión de transacciones

Utilizando procedimientos almacenados, puedes asegurarte de que varias operaciones se ejecuten como una única transacción. Por ejemplo, en un sistema bancario, un procedimiento almacenado puede gestionar tanto las acciones de débito como de crédito en una transferencia de fondos, asegurándose de que ambas acciones tengan éxito o fallen juntas.

-- Create a procedure to transfer funds between accounts
CREATE PROCEDURE TransferFunds
    @SenderAccount INT,
    @ReceiverAccount INT,
    @Amount DECIMAL(10, 2)
AS
BEGIN
    BEGIN TRANSACTION;  -- Start a transaction to ensure atomicity
    -- Deduct the specified amount from the sender's account balance
    UPDATE Accounts SET Balance = Balance - @Amount WHERE AccountID = @SenderAccount;
    -- Add the specified amount to the receiver's account balance
    UPDATE Accounts SET Balance = Balance + @Amount WHERE AccountID = @ReceiverAccount;
    -- Check for errors and rollback if any occurred; otherwise, commit the transaction
    IF @@ERROR <> 0
        ROLLBACK TRANSACTION;  -- Undo all changes if an error occurred
    ELSE
        COMMIT TRANSACTION;    -- Confirm changes if no errors
END;

Control de acceso y seguridad de los datos

También puedes utilizar SQL almacenado para controlar el acceso a información sensible. Por ejemplo, un procedimiento almacenado puede limitar el acceso directo a la tabla permitiendo a los usuarios llamar a un procedimiento que sólo recupere los campos relevantes, como los saldos de las cuentas, sin los detalles de las transacciones.

-- Create a procedure to retrieve account balance, with authorization check
CREATE PROCEDURE GetAccountBalance
    @AccountID INT, 
    @UserID INT 
AS
BEGIN
    -- Check if the account exists and is owned by the specified user
    IF EXISTS (SELECT 1 FROM Accounts WHERE AccountID = @AccountID AND UserID = @UserID)
        -- If authorized, select and return the account balance
        SELECT Balance FROM Accounts WHERE AccountID = @AccountID;
    ELSE
        -- If unauthorized, throw an error
        THROW 50000, 'Unauthorized access.', 1;
END;

Procesos de migración de datos y ETL

Los procedimientos almacenados también se utilizan para cargar, transformar y migrar datos entre sistemas. Un procedimiento almacenado puede automatizar la extracción de datos de una base de datos de origen, transformarlos según sea necesario e insertarlos en una tabla de destino, simplificando la integración de datos para la elaboración de informes o análisis.

CREATE PROCEDURE ETLProcess
AS
BEGIN
    -- Extract
    INSERT INTO StagingTable
    SELECT * FROM SourceTable WHERE Condition;
    -- Transform
    UPDATE StagingTable SET ColumnX = TransformationLogic(ColumnX);
    -- Load
    INSERT INTO TargetTable
    SELECT * FROM StagingTable;
END;

Buenas prácticas para los procedimientos almacenados

Escribir procedimientos almacenados eficientes y fáciles de mantener garantiza el rendimiento óptimo de tu base de datos. A continuación te damos algunos consejos para escribir procedimientos almacenados para tus bases de datos SQL.

• Utiliza convenciones de nomenclatura coherentes: Para que los procedimientos almacenados sean fáciles de identificar y comprender, utiliza un formato de denominación coherente y descriptivo. Además, evita el prefijo sp_ en SQL Server, reservado a los procedimientos del sistema, para evitar posibles conflictos y problemas de rendimiento.

• Implementa el Tratamiento de Errores: Envuelve las sentencias SQL en bloques TRY...CATCH para detectar y gestionar errores y mantener la integridad de los datos. 

• Optimiza el rendimiento: Minimiza el uso de cursores, ya que pueden ser lentos y consumir muchos recursos. En su lugar, intenta utilizar operaciones basadas en conjuntos, que suelen ser más eficientes. Además, indexa las columnas de uso frecuente y evita las uniones complejas en tablas grandes para reducir la sobrecarga de memoria y mejorar la eficacia.

• Parametrizar Procedimientos Almacenados: Utiliza parámetros en lugar de valores codificados para poder pasar valores dinámicos a tu procedimiento, haciéndolo más flexible y reutilizable.

Consulta nuestro curso de SQL Intermedio para saber más sobre el uso de funciones agregadas y uniones para filtrar datos. Además, prueba nuestras pistas de habilidades Fundamentos de SQL Server y Fundamentos de SQL para mejorar tus habilidades de unión de tablas y análisis de datos.

Conclusión

Los procedimientos almacenados SQL mejoran la reutilización del código y la optimización del rendimiento en la gestión de bases de datos. Los procedimientos almacenados también mejoran la seguridad de la base de datos mediante un acceso controlado y garantizando la integridad de los datos. Como profesional de los datos, te animo a que practiques la creación y ejecución de procedimientos almacenados para dominar las mejores prácticas de gestión de bases de datos.

Si estás interesado en convertirte en un analista de datos competente, consulta nuestro itinerario profesional de Analista de Datos Asociado en SQL para aprender las habilidades necesarias. El curso Informes en SQL también es adecuado si quieres aprender a crear cuadros de mando profesionales utilizando SQL. Por último, recomiendo obtener la Certificación de Asociado SQL para demostrar tu dominio del uso de SQL para el análisis de datos y destacar entre los demás profesionales de datos.