Ejecutar funciones definidas por el usuario (motor de base de datos)

  • Artículo

Actualizado: 14 de abril de 2006

Las funciones definidas por el usuario se pueden invocar en consultas o en otras instrucciones o expresiones, como columnas calculadas o expresiones de cadena. Las funciones con valores escalares se pueden ejecutar mediante la instrucción EXECUTE.

Invocar funciones definidas por el usuario que devuelven un valor escalar

Puede invocar una función definida por el usuario que devuelve un valor escalar en cualquier parte de una expresión escalar del mismo tipo de datos que se permite en las instrucciones Transact-SQL. Las funciones con valores escalares deben invocarse como mínimo con el nombre de dos partes de la función. Para obtener más información sobre los nombres con varias partes, vea Convenciones de sintaxis de Transact-SQL (Transact-SQL).

Consultas

En estas ubicaciones se permiten funciones definidas por el usuario que devuelven valores escalares:

  • Como expression de select_list de una instrucción SELECT:

    USE AdventureWorks;
GO
SELECT ProductID, ListPrice, dbo.ufnGetProductDealerPrice(ProductID, StartDate) AS DealerPrice,
    StartDate, EndDate
FROM Production.ProductListPriceHistory
WHERE ListPrice > .0000
ORDER BY ProductID, StartDate;
GO

  • Como expression o string_expression en un predicado de cláusula WHERE o HAVING:

    USE AdventureWorks;
GO
SELECT ProductID, ListPrice, StartDate, EndDate
FROM Production.ProductListPriceHistory
WHERE dbo.ufnGetProductDealerPrice(ProductID, StartDate) > .0000
ORDER BY ProductID, StartDate;
GO

  • Como group_by_expression en una cláusula GROUP BY.

  • Como order_by_expression en una cláusula ORDER BY.

  • Como expression en la cláusula SET de una instrucción UPDATE:

    USE AdventureWorks;
GO
UPDATE Production.ProductListPriceHistory
SET ListPrice = dbo.ufnGetProductDealerPrice(ProductID, StartDate)
WHERE ProductID > 900;
GO

  • Como expression en la cláusula VALUES de una instrucción INSERT:
    Las funciones definidas por el usuario a las que se hace referencia en estas ubicaciones se ejecutan lógicamente una vez por fila.

Restricciones CHECK

Las funciones definidas por el usuario que devuelven valores escalares se pueden invocar en restricciones CHECK si los valores de argumento transferidos a la función sólo hacen referencia a columnas o constantes en la tabla. Cada vez que el procesador de consultas comprueba la restricción, el procesador de consultas llama a la función con los valores de argumento asociados a la fila que se está comprobando actualmente. El propietario de una tabla también debe ser el propietario de la función definida por el usuario invocada por una restricción CHECK de una tabla.

Definiciones DEFAULT

Las funciones definidas por el usuario se pueden invocar como constant_expression de definiciones DEFAULT si los valores de argumentos transferidos a la función sólo contienen constantes. El propietario de la tabla también debe ser el propietario de la función definida por el usuario invocada por una definición DEFAULT de una tabla.

Columnas calculadas

Es posible invocar funciones mediante columnas calculadas si los valores de argumentos transferidos a la función sólo hacen referencia a columnas o constantes de una tabla. El propietario de la tabla también debe ser el propietario de la función definida por el usuario invocada por una columna calculada de una tabla.

Operadores de asignación

Los operadores de asignación (left_operand = right_operand) pueden invocar funciones definidas por el usuario que devuelven un valor escalar en la expresión especificada como operando derecho.

Instrucciones de control de flujo

Las funciones definidas por el usuario que devuelven valores escalares se pueden invocar mediante instrucciones de control de flujo en sus expresiones booleanas.

Expresiones CASE

Las funciones definidas por el usuario que devuelven un valor escalar se pueden invocar en cualquiera de las expresiones CASE.

Instrucciones PRINT

Las funciones definidas por el usuario que devuelven una cadena de caracteres se pueden invocar como expresión string_expr de instrucciones PRINT.

Funciones y procedimientos almacenados

  • Los argumentos de funciones también pueden hacer referencia a una función definida por el usuario que devuelve un valor escalar.
  • Las instrucciones RETURN integer_expression de procedimientos almacenados pueden llamar a funciones definidas por el usuario que devuelven un entero como integer_expression.
  • Las instrucciones RETURN return_type_spec de funciones definidas por el usuario pueden invocar a funciones definidas por el usuario que devuelven un tipo de datos escalar como return_type_spec, con tal de que el valor devuelto por la función definida por el usuario que se invoca pueda convertirse implícitamente en el tipo de datos de vuelta de la función que realiza la invocación.

Ejecutar funciones definidas por el usuario que devuelven un valor escalar

Puede ejecutar funciones definidas por el usuario que devuelven valores escalares de la misma forma que si se tratara de procedimientos almacenados. Cuando ejecute una función definida por el usuario que devuelve un valor escalar, los parámetros se especifican del mismo modo que para los procedimientos almacenados:

  • Los valores de argumento no se incluyen entre paréntesis.
  • Se pueden especificar nombres de parámetros.
  • Si se especifican nombres de parámetros, los valores de argumento no tienen que estar en la misma secuencia que los parámetros.

En el siguiente ejemplo se crea una función definida por el usuario que devuelve un valor escalar decimal.

IF OBJECT_ID(N'dbo.ufn_CubicVolume', N'FN') IS NOT NULL
    DROP FUNCTION dbo.ufn_CubicVolume;
GO
CREATE FUNCTION dbo.ufn_CubicVolume
-- Input dimensions in centimeters.
   (@CubeLength decimal(4,1), @CubeWidth decimal(4,1),
    @CubeHeight decimal(4,1) )
RETURNS decimal(12,3) -- Cubic Centimeters.
WITH SCHEMABINDING
AS
BEGIN
   RETURN ( @CubeLength * @CubeWidth * @CubeHeight )
END;
GO

El siguiente ejemplo ejecuta la función dbo.ufn_CubicVolume. Mediante la instrucción EXECUTE de Transact-SQL, los argumentos se identifican en un orden distinto al de los parámetros en la definición de la función:

DECLARE @MyDecimalVar decimal(12,3);
EXEC @MyDecimalVar = dbo.ufn_CubicVolume @CubeLength = 12.3,
                        @CubeHeight = 4.5, @CubeWidth = 4.5;
SELECT @MyDecimalVar;
GO

El siguiente ejemplo ejecuta la función dbo.ufn_CubicVolume sin especificar los nombres de parámetros:

DECLARE @MyDecimalVar decimal(12,3);
EXEC @MyDecimalVar = dbo.ufn_CubicVolume 12.3, 4.5, 4.5;
SELECT @MyDecimalVar;
GO

También puede utilizar la sintaxis CALL de ODBC para ejecutar la función dbo.ufn_CubicVolume desde aplicaciones OLE DB u ODBC:

-- First use SQLBindParam to bind the return value parameter marker
-- to a program variable of the appropriate type
SQLExecDirect(hstmt,
              "{ CALL ? = dbo.ufn_CubicVolume(12.3, 4.5, 4.5) }",
              SQL_NTS);

Invocar funciones definidas por el usuario que devuelven un tipo de datos table

Puede invocar una función definida por el usuario que devuelve una table, donde se permiten expresiones de tabla en la cláusula FROM de instrucciones SELECT, INSERT, UPDATE o DELETE. Una invocación de una función definida por el usuario que devuelve una tabla puede estar seguida de un alias de tabla opcional. En el ejemplo siguiente se ilustra la llamada a la función con valores de tabla dbo.ufnGetContactInformation en la cláusula FROM de una instrucción SELECT.

USE AdventureWorks;
GO
SELECT ContactID, FirstName, LastName, JobTitle, ContactType
FROM dbo.ufnGetContactInformation(2200);
GO
SELECT ContactID, FirstName, LastName, JobTitle, ContactType
FROM dbo.ufnGetContactInformation(5);
GO

Cuando una función definida por el usuario que devuelve una tabla (table) se invoca en la cláusula FROM de una subconsulta, los argumentos de la función no pueden hacer referencia a columnas de la consulta externa.

Los cursores estáticos de sólo lectura son el único tipo de cursor que se puede abrir en una instrucción SELECT cuya cláusula FROM hace referencia a una función definida por el usuario que devuelve una tabla.

Una instrucción SELECT que hace referencia a una función definida por el usuario que devuelve una table invoca la función una sola vez.

Invocar funciones integradas con valores de tabla

Existen varias funciones integradas con valores de tabla que devuelven un valor de tabla. La invocación de estas funciones integradas definidas por el usuario pueden estar no calificadas o utilizar el calificador de esquema sys. Debe utilizar el calificador de esquema sys para funciones integradas con valores de tabla, porque evita conflictos con funciones definidas por el usuario con el mismo nombre. En el siguiente ejemplo se muestra cómo invocar la función integrada del sistema fn_helpcollations.

SELECT *
FROM sys.fn_helpcollations();
GO

Usar sugerencias en funciones con valores de tabla

Al crear una función definida por el usuario, puede aplicar una sugerencia de tabla en las consultas que conforman la definición de la función. Las sugerencias que se aplican a vistas que hacen referencia a funciones con valores de tabla de Transact-SQL también se aplican a las funciones. Estas funciones pueden entrar en conflicto con las sugerencias de la definición de la función. Para obtener más información, vea Resolución de vistas.

No puede aplicar sugerencias en vistas que hacen referencia a funciones con valores de tabla de CLR.

[!NOTA] La posibilidad de que el Database Engine (Motor de base de datos) aplique sugerencias de vistas a funciones con valores de tabla de múltiples instrucciones que forman parte de la definición de vista se quitará en una versión futura de SQL Server.

No se puede aplicar una sugerencia de tabla al resultado de ninguna función con valores de tabla de la cláusula FROM de una consulta.

Vea también

Conceptos

Crear funciones definidas por el usuario (motor de base de datos)
Crear funciones CLR
Crear funciones de agregado definidas por el usuario
Ver funciones definidas por el usuario

Ayuda e información

Obtener ayuda sobre SQL Server 2005

Historial de cambios

Versión Historial

14 de abril de 2006
Contenido nuevo:
  • Se agregó el texto que indica que las funciones con valores escalares deben invocarse como mínimo con el nombre de dos partes de la función.