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. La interfaz es el contrato de parámetros, resultados y comportamientos que conoce quien lo llama.

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. SQL dinámico es código SQL construido durante la ejecución. Evitamos formarlo concatenando texto recibido; cuando sea imprescindible, parametrizamos la ejecución, es decir, enviamos los valores separados del texto de la consulta.

Los filtros opcionales pueden producir planes de ejecución —las estrategias elegidas por SQL Server— adecuados para unos valores y pobres para otros. Medimos con datos representativos antes de aplicar soluciones como recompilación, que crea un plan nuevo para la ejecución actual, o SQL dinámico.

Buscar en SQL Server