Funciones Valoradas en Tabla de SQL Server

Resumen: en este tutorial, aprenderá a utilizar la función valorada en tabla de SQL Server, incluyendo la función valorada en tabla en línea y las funciones valoradas en varios estados.

Qué es una función valorada en tabla en SQL Server

Una función valorada en tabla es una función definida por el usuario que devuelve datos de un tipo de tabla. El tipo de retorno de una función con valor de tabla es una tabla, por lo tanto, puede utilizar la función con valor de tabla igual que utilizaría una tabla.

Creación de una función table-valued

El siguiente ejemplo de declaración crea una función table-valued que devuelve una lista de productos que incluye el nombre del producto, el año del modelo y el precio de lista para un año del modelo específico:

CREATE FUNCTION udfProductInYear ( @model_year INT)RETURNS TABLEASRETURN SELECT product_name, model_year, list_price FROM production.products WHERE model_year = @model_year;
Code language: SQL (Structured Query Language) (sql)

La sintaxis es similar a la que crea una función definida por el usuario.

El RETURNS TABLE especifica que la función devolverá una tabla. Como puedes ver, no hay ninguna sentencia BEGIN...END. La sentencia simplemente consulta los datos de la tabla production.products.

La función udfProductInYear acepta un parámetro llamado @model_year de tipo INT. Devuelve los productos cuyos años de modelo son iguales al parámetro @model_year.

Una vez creada la función con valores de tabla, la encontrarás en Programabilidad > Funciones > con valores de tabla como se muestra en la siguiente imagen:

La función anterior devuelve el conjunto de resultados de una sola sentencia SELECT, por lo tanto, también se conoce como una función valorada por tabla en línea.

Ejecución de una función con valor de tabla

Para ejecutar una función con valor de tabla, se utiliza en la cláusula FROM de la sentencia SELECT:

SELECT * FROM udfProductInYear(2017);
Code language: SQL (Structured Query Language) (sql)

En este ejemplo, seleccionamos los productos cuyo año del modelo es 2017.

También puedes especificar qué columnas se devolverán desde la función de valores de la tabla de la siguiente manera:

SELECT product_name, list_priceFROM udfProductInYear(2018);
Code language: SQL (Structured Query Language) (sql)

Aquí tienes la salida parcial:

Modificar una función valorada en tabla

Para modificar una función valorada en tabla, se utiliza la palabra clave ALTER en lugar de CREATE. El resto del script es el mismo.

Por ejemplo, la siguiente sentencia modifica la función udfProductInYear cambiando el parámetro existente y añadiendo un parámetro más:

ALTER FUNCTION udfProductInYear ( @start_year INT, @end_year INT)RETURNS TABLEASRETURN SELECT product_name, model_year, list_price FROM production.products WHERE model_year BETWEEN @start_year AND @end_year
Code language: SQL (Structured Query Language) (sql)

La función udfProductInYear devuelve ahora los productos cuyo año de modelo esté entre un año inicial y un año final.

La siguiente sentencia llama a la función udfProductInYear para obtener los productos cuyos años de modelo están entre 2017 y 2018:

SELECT product_name, model_year, list_priceFROM udfProductInYear(2017,2018)ORDER BY product_name;
Code language: SQL (Structured Query Language) (sql)

Aquí está la salida parcial:

Funciones valoradas en tablas de múltiples estados (MSTVF).valued functions (MSTVF)

Una función multi-statement table-valued o MSTVF es una función table-valued que devuelve el resultado de múltiples sentencias.

La función valorada en tabla de múltiples sentencias es muy útil porque puede ejecutar múltiples consultas dentro de la función y agregar los resultados en la tabla devuelta.

Para definir una función valorada en tabla de múltiples sentencias, se utiliza una variable de tabla como valor de retorno. Dentro de la función, ejecuta una o más consultas e inserta los datos en esta variable de tabla.

La siguiente función udfContacts() combina los empleados y los clientes en una única lista de contactos:

CREATE FUNCTION udfContacts() RETURNS @contacts TABLE ( first_name VARCHAR(50), last_name VARCHAR(50), email VARCHAR(255), phone VARCHAR(25), contact_type VARCHAR(20) )ASBEGIN INSERT INTO @contacts SELECT first_name, last_name, email, phone, 'Staff' FROM sales.staffs; INSERT INTO @contacts SELECT first_name, last_name, email, phone, 'Customer' FROM sales.customers; RETURN;END;
Code language: SQL (Structured Query Language) (sql)

La siguiente sentencia ilustra cómo ejecutar una función de valores de tabla multiestado udfContacts:

SELECT * FROM udfContacts();
Code language: SQL (Structured Query Language) (sql)

Salida:

Cuándo utilizar funciones con valores de tabla

Típicamente utilizamos funciones con valores de tabla como vistas parametrizadas. En comparación con los procedimientos almacenados, las funciones con valores de tabla son más flexibles porque podemos utilizarlas dondequiera que se utilicen tablas.

En este tutorial, ha aprendido sobre la función con valores de tabla de SQL Server, incluyendo las funciones con valores de tabla en línea y las funciones con valores de tabla de varias declaraciones.