Funciones

🧩 Conceptos clave

Existen tres tipos principales de User Defined Functions (UDF) o funciones definidas por el usuario en SQL Server:

1. Funciones escalares: Devuelven un único valor.
2. Funciones en línea (Inline): Devuelven una tabla a partir de una única sentencia SELECT.
3. Funciones de múltiples sentencias: Devuelven una tabla a partir de una o varias sentencias T-SQL más complejas.

---

🔢 Funciones Escalares

Devuelven un único valor que puede ser de cualquier tipo de dato. Puedes usarlas en cualquier sentencia, incluyendo SELECT, WHERE o SET. Para invocarlas, siempre debes prefijar el nombre de la función con el esquema al que pertenece, generalmente dbo.

¡Importante!

Las funciones no pueden ejecutar sentencias que modifiquen datos, como INSERT, UPDATE o DELETE. Su propósito es solo devolver información.

Estructura

CREATE FUNCTION <Scalar_Function_Name, sysname, FunctionName>
(
   <@Param1, sysname, @p1> <Data_Type_For_Param1, , int>, ...
)
RETURNS <Function_Data_Type, ,int>
AS
BEGIN
   -- Lógica T-SQL
   RETURN <Valor_a_devolver>
END

Ejemplo 1: Creación de la Función

Este ejemplo crea una función para calcular el precio de un artículo después de aplicar un descuento.

CREATE FUNCTION dbo.fn_AplicaDescuento
(
   @idarticulo VARCHAR(20),
   @descuento DECIMAL(10,2)
)
RETURNS DECIMAL(10,2)
AS
BEGIN
   DECLARE @precio DECIMAL(10,2)

   SELECT @precio = precio
   FROM Articulos
   WHERE ID = @idarticulo

   RETURN @precio - @descuento
END

Ejemplo 2: Uso en una Sentencia SELECT

Aquí, la función se utiliza para añadir una columna con el precio reducido directamente en la consulta.

SELECT ID,
       nom,
       descripcion,
       precio,
       -- Ejecución de la función
       dbo.fn_AplicaDescuento(ID, 10) AS precioReducido
FROM Articulos

Ejemplo 3: Uso con Variables

Puedes asignar el resultado de una función escalar a una variable T-SQL.

DECLARE @idarticulo VARCHAR(20),
        @resultado DECIMAL(10,2)

SET @idarticulo = '33' -- Corregido, los valores VARCHAR deben ir entre comillas
SET @resultado = dbo.fn_AplicaDescuento(@idarticulo, 65.90)

PRINT @resultado

El resultado de la ejecución del script con la función escalar será 58.46, que es el resultado de 124.36 - 65.90.

---

📊 Funciones en Línea (Inline)

Estas funciones devuelven una tabla que es el resultado de una única sentencia SELECT. Son muy eficientes porque el optimizador de consultas de SQL Server las trata de manera similar a las vistas parametrizadas.

Dato útil

A diferencia de una vista, puedes pasarle parámetros a una función en línea para filtrar los resultados, lo que las hace mucho más flexibles.

Estructura

CREATE FUNCTION <Inline_Function_Name, sysname, FunctionName>
(
   <@param1, sysname, @p1> <Data_Type_For_Param1, , int>,...
)
RETURNS TABLE
AS
RETURN
(
   -- Única sentencia Transact SQL
   SELECT ... FROM ... WHERE ...
)

Ejemplo 1: Creación de la Función

Función que retorna una tabla con los movimientos de pedidos para un estado de proposición específico.

CREATE FUNCTION dbo.fn_MovimientosCuenta
(
   @idEstadoProposicion VARCHAR(20)
)
RETURNS TABLE
AS
RETURN
(
   SELECT p.Id, c.razonSocial as [Razón Social], a.nom as [Nombre de Artículo], p.fecha as [Fecha], p.cant as [Cantidad], p.importe as [Importe], e.Estado  , ep.estado_proposicion
   FROM Pedidos as p
   INNER JOIN Clientes as c ON p.codCliente = c.Id
   INNER JOIN Articulos as a ON p.codArticulo = a.Id
   INNER JOIN EstadoPedidos as e ON p.codEstadoPedido = e.Id
   INNER JOIN Proposiciones as prop ON prop.ID = p.codProposicion
   INNER JOIN Estados_proposicion as ep ON ep.ID = prop.codEstado_proposicion
   WHERE ep.ID = @idEstadoProposicion
)

Ejemplo 2: Invocación

Para usarla, simplemente la llamas como si fuera una tabla o vista.

SELECT * FROM dbo.fn_MovimientosCuenta(1)

---

📈 Funciones de Múltiples Sentencias

Son funciones que devuelven una tabla, pero a diferencia de las funciones en línea, pueden contener múltiples sentencias T-SQL dentro del bloque BEGIN...END. Esto permite lógicas más complejas, como el uso de cursores, bucles o la inserción de datos en una variable de tabla para construir el resultado.

Estructura

CREATE FUNCTION <Table_Function_Name, sysname, FunctionName>
(
    <@param1, sysname, @p1> <data_type_for_param1, , int>, ...
)
RETURNS
<@Table_Variable_Name, sysname, @Table_Var> TABLE
(
    <Column_1, sysname, c1> <Data_Type_For_Column1, , int>,
    <Column_2, sysname, c2> <Data_Type_For_Column2, , int>
)
AS
BEGIN
    -- Múltiples sentencias T-SQL para cargar la tabla
    INSERT INTO @Table_Variable_Name (...)
    SELECT ...

    -- ...

    RETURN
END

Ejemplo 1: Cuentas y Movimientos

Este ejemplo utiliza un cursor para iterar sobre las 3 cuentas con mayor saldo y luego inserta los datos de la cuenta y sus 3 últimos movimientos en la tabla de resultados.

/*
Esta función busca las tres cuentas con mayor saldo y obtiene los tres últimos movimientos de cada una de estas cuentas.
*/
CREATE FUNCTION dbo.fn_CuentaMovimientos()
RETURNS @datos TABLE (
    NumCuenta varchar(20),
    Saldo decimal(10,2),
    Saldo_anterior decimal(10,2),
    Saldo_posterior decimal(10,2),
    Importe_Movimiento decimal(10,2),
    FxMovimiento datetime
)
AS
BEGIN
    -- Variables necesarias para la lógica de la función
    DECLARE @idcuenta int,
            @numcuenta varchar(20),
            @saldo decimal(10,2)

    -- Cursor con las 3 cuentas de mayor saldo
    DECLARE CDATOS CURSOR FOR
    SELECT TOP 3 IDCUENTA, NUMCUENTA, SALDO
    FROM CUENTAS
    ORDER BY SALDO DESC

    OPEN CDATOS
    FETCH CDATOS INTO @idcuenta, @numcuenta, @saldo

    -- Recorremos el cursor
    WHILE (@@FETCH_STATUS = 0)
    BEGIN
        -- Insertamos la cuenta en la variable de salida (subcabeceras)
        INSERT INTO @datos
        (NumCuenta, Saldo)
        VALUES
        (@numcuenta, @saldo)

        -- Insertamos los tres últimos movimientos de la cuenta (subfilas)
        INSERT INTO @datos
        (Saldo_anterior, Saldo_posterior, Importe_Movimiento, FxMovimiento )
        SELECT TOP 3
            SALDO_ANTERIOR, SALDO_POSTERIOR, IMPORTE, FXMOVIMIENTO
        FROM MOVIMIENTOS
        WHERE IDCUENTA = @idcuenta
        ORDER BY FXMOVIMIENTO DESC

        -- Vamos a la siguiente cuenta
        FETCH CDATOS INTO @idcuenta, @numcuenta, @saldo
    END

    CLOSE CDATOS;
    DEALLOCATE CDATOS;

    RETURN
END

---

Ejemplo 2: Listar Permisos

Este ejemplo muestra cómo se puede usar un cursor para listar los roles y sus permisos asociados. Es un buen ejemplo para ilustrar una lógica más compleja de carga de datos en una tabla de salida.

CREATE FUNCTION dbo.fn_listarPermisos()
--- definimos la estructura de la tabla que va a devolver la función
RETURNS @datos TABLE(
    rol varchar(100),
    Descripcion varchar(100),
    Permiso varchar(100)
)
AS
BEGIN
-- declaramos las variables que necesitaremos para la lógica de la función
DECLARE @rol varchar(100),
        @idRol int,
        @descripcion varchar(100),
        @permiso varchar(100)

-- declaramos el cursor que recorrerá las filas necesarias para el resultado requerido
        DECLARE cDatos CURSOR FOR
        SELECT ID,  r.nombre_rol, desc_rol
        FROM Roles r
        ORDER BY r.nombre_rol DESC

-- abrimos el cursor y lo iniciamos en la primera fila
        OPEN cDatos
        FETCH cDatos INTO  @idRol, @rol, @descripcion
-- iniciamos el bucle que recorrera filas mientras haya
        WHILE (@@FETCH_STATUS = 0)
        BEGIN
-- inserta los valores rol y Descripcion del cursor en la tabla @datos
-- Permiso quedará reflejada como NULL
            INSERT INTO @datos (rol, Descripcion)
            VALUES(@rol, @descripcion)

-- inserta el valor del permiso de la consulta propuesta en la tabla @datos
-- el resto de columnas se reflejan como NULL
            INSERT INTO @datos (Permiso)
            SELECT p.nombre FROM Permisos p
            INNER JOIN Permisos_roles pr ON pr.ID_Permiso = p.ID
            INNER JOIN Roles r ON r.ID = pr.ID_rol
            WHERE r.ID = @idRol

            FETCH cDatos INTO @idRol, @rol, @descripcion
        END

        CLOSE cDatos;
        DEALLOCATE cDAtos;

    RETURN;
END