Procedimientos almacenados: encapsular una operación con parámetros

Cuando una consulta debe ejecutarse desde varias aplicaciones o necesita combinar validaciones, lecturas y modificaciones, un procedimiento permite darle un nombre y una interfaz estable.

Un procedimiento puede recibir parámetros, ejecutar varias instrucciones y devolver uno o más conjuntos de resultados. No es solamente una consulta guardada: también puede coordinar una operación completa.

Crear o actualizar el procedimiento

CREATE OR ALTER PROCEDURE dbo.usp_ProductosPorCategoria
    @IdCategoria SMALLINT,
    @SoloActivos BIT = 1
AS
BEGIN
    SET NOCOUNT ON;

    SELECT
        p.IdProducto,
        p.Nombre,
        p.Precio,
        p.Existencia
    FROM dbo.Producto AS p
    WHERE p.IdCategoria = @IdCategoria
      AND (@SoloActivos = 0 OR p.Activo = 1)
    ORDER BY p.Nombre;
END;
GO

CREATE OR ALTER permite utilizar el mismo script tanto al crear como al modificar el objeto. La instrucción debe iniciar su lote; GO es el separador que emplea la herramienta cliente.

El parámetro @SoloActivos tiene un valor predeterminado. Quien llama puede omitirlo o enviar otro valor de forma explícita.

Ejecutarlo

EXEC dbo.usp_ProductosPorCategoria
    @IdCategoria = 2;

EXEC dbo.usp_ProductosPorCategoria
    @IdCategoria = 2,
    @SoloActivos = 0;

Nombrar los parámetros hace la llamada más legible y menos dependiente de su posición.

Parámetros de salida

Un parámetro OUTPUT permite devolver un valor puntual además de los conjuntos de filas:

CREATE OR ALTER PROCEDURE dbo.usp_ContarProductosActivos
    @IdCategoria SMALLINT,
    @Cantidad INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    SELECT @Cantidad = COUNT(*)
    FROM dbo.Producto
    WHERE IdCategoria = @IdCategoria
      AND Activo = 1;
END;
GO

DECLARE @Total INT;

EXEC dbo.usp_ContarProductosActivos
    @IdCategoria = 2,
    @Cantidad = @Total OUTPUT;

SELECT @Total AS ProductosActivos;

La palabra OUTPUT se declara en el procedimiento y se repite en la llamada. Si falta en la llamada, la variable exterior no recibe el valor.

Resultado, salida y retorno

Un SELECT devuelve filas. Un parámetro OUTPUT devuelve un valor mediante la interfaz del procedimiento. RETURN devuelve un entero que suele reservarse para un estado de ejecución, no para transportar datos de negocio.

La aplicación que consume el procedimiento debe conocer qué conjuntos y parámetros espera. Cambiar nombres, tipos u orden de columnas puede romper consumidores aunque el procedimiento siga compilando.

Qué conviene comprobar

Validamos tipos, valores predeterminados, permisos y comportamiento ante errores. También evitamos construir SQL dinámico concatenando texto recibido; cuando sea imprescindible, parametrizamos la ejecución.

Los filtros opcionales pueden producir planes adecuados para unos valores y pobres para otros. Medimos con datos representativos antes de aplicar soluciones como recompilación o SQL dinámico.

Relacionado con

• Variables: reciben parámetros y resultados puntuales.
• TRY/CATCH: controla errores dentro de una operación.
• Transacciones: agrupan modificaciones que deben confirmarse juntas.
• Funciones: devuelven un valor o un conjunto y tienen restricciones diferentes.