Crea una función definida por el usuario. Es una rutina guardada de Transact-SQL o de Common Language Runtime (CLR) que devuelve un valor. Las funciones definidas por el usuario no se pueden utilizar para realizar acciones que modifican el estado de la base de datos. Las funciones definidas por el usuario, como las funciones de sistema, se pueden llamar desde una consulta. Las funciones escalares se pueden ejecutar con la instrucción EXECUTE, igual que los procedimientos almacenados.
Las funciones definidas por el usuario son con valores escalares o con valores de tabla. Son funciones con valores escalares si la cláusula RETURNS especificó uno de los tipos de datos escalares. Las funciones con valores escalares se pueden definir utilizando varias instrucciones Transact-SQL.
Son funciones con valores de tabla si la cláusula RETURNS especificó TABLE. Según cómo se haya definido el cuerpo de la función, las funciones con valores de tabla se pueden clasificar en funciones en línea o de múltiples instrucciones. Para obtener más información, vea Funciones definidas por el usuario con valores de tabla.
Las siguientes instrucciones son válidas en una función:
-
Instrucciones de asignación.
-
Instrucciones de control de flujo, excepto las instrucciones TRY...CATCH.
-
Instrucciones DECLARE que definen variables de datos locales y cursores locales.
-
Instrucciones SELECT que contienen listas de selección con expresiones que asignan valores a variables locales.
-
Operaciones de cursor que hacen referencia a cursores locales que se declaran, abren, cierran y cuya asignación se cancela en la función. Sólo se permiten las instrucciones FETCH que asignan valores a las variables locales mediante la cláusula INTO; no se permiten las instrucciones FETCH que devuelven los datos al cliente.
-
Instrucciones INSERT, UPDATE y DELETE que modifican variables table locales.
-
Instrucciones EXECUTE que llaman a procedimientos almacenados extendidos.
-
Para obtener más información, vea Crear funciones definidas por el usuario (motor de base de datos).
Anidar funciones definidas por el usuario
Las funciones definidas por el usuario se pueden anidar; es decir, una función definida por el usuario puede llamar a otra. El nivel de anidamiento aumenta cuando se empieza a ejecutar la función llamada y disminuye cuando se termina de ejecutar la función llamada. Las funciones definidas por el usuario se pueden anidar hasta un máximo de 32 niveles. Si se sobrepasan los niveles máximos de anidamiento, la cadena completa de funciones de llamada produce un error.
.gif) Nota: |
|---|
|
Cualquier referencia a código administrado desde una función Transact-SQL definida por el usuario cuenta como uno de los 32 niveles de anidamiento. Los métodos a los que se llama desde el código administrado no cuentan para este límite.
|
Propiedades de las funciones
En versiones anteriores de SQL Server, las funciones se clasificaban únicamente como deterministas o no deterministas. En SQL Server 2005, las funciones tienen las siguientes propiedades. Los valores de estas propiedades determinan si las funciones se pueden utilizar en columnas calculadas, que pueden ser persistentes o indizadas.
|
Propiedad
|
Descripción
|
Notas
|
|---|
|
IsDeterministic
|
La función es determinista o no determinista.
|
En las funciones deterministas, se permite el acceso a los datos locales. Por ejemplo, se consideran deterministas las funciones que devuelven siempre el mismo resultado al llamarlas, utilizando un conjunto específico de valores de entrada y con el mismo estado de la base de datos.
|
|
IsPrecise
|
La función es precisa o imprecisa.
|
Las funciones imprecisas pueden contener operaciones, como operaciones de punto flotante.
|
|
IsSystemVerified
|
SQL Server puede comprobar las propiedades de precisión y determinismo de la función.
|
|
|
SystemDataAccess
|
La función tiene acceso a los datos del sistema (catálogos del sistema o tablas del sistema virtuales) en la instancia local de SQL Server.
|
|
|
UserDataAccess
|
La función tiene acceso a los datos del usuario en la instancia local de SQL Server.
|
Incluye las tablas temporales y definidas por el usuario, pero no las variables de tabla.
|
SQL Server determina automáticamente las propiedades de precisión y determinismo de las funciones Transact-SQL. Para obtener más información, vea Directrices para el diseño de funciones definidas por el usuario. El usuario puede especificar las propiedades de acceso a datos y determinismo de las funciones CLR. Para obtener más información, vea Overview of CLR Integration Custom Attributes.
Para ver los valores actuales de estas propiedades, utilice OBJECTPROPERTYEX.
Indizar columnas calculadas que llaman a funciones definidas por el usuario
Una columna calculada que llama a una función definida por el usuario se puede utilizar en un índice cuando la función definida por el usuario tiene los siguientes valores de propiedades:
-
IsDeterministic = true
-
IsSystemVerified = true (a menos que la columna calculada sea persistente)
-
UserDataAccess = false
-
SystemDataAccess = false
Para obtener más información, vea Crear índices en columnas calculadas.
Llamar a procedimientos almacenados extendidos desde funciones
Cuando se llama a un procedimiento almacenado extendido desde una función, no se puede devolver al cliente el conjunto de resultados. Cualquier API ODS que devuelva conjuntos de resultados al cliente devolverá FAIL. El procedimiento almacenado extendido se puede volver a conectar a una instancia de SQL Server; sin embargo, no debería intentar combinar la misma transacción como la función que invocó el procedimiento almacenado extendido.
Como ocurre con las invocaciones desde un proceso por lotes o un procedimiento almacenado, el procedimiento almacenado extendido se ejecutará en el contexto de la cuenta de seguridad de Windows en la que se ejecuta SQL Server. El propietario del procedimiento almacenado debe tener esto en cuenta al otorgar permisos EXECUTE a los usuarios.
Invocación de funciones
Se pueden invocar funciones con valores escalares cuando se utilizan expresiones escalares. Esto incluye las columnas calculadas y las definiciones de restricciones CHECK. Las funciones con valores escalares también se pueden ejecutar con la instrucción EXECUTE. 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 de varias partes, vea Convenciones de sintaxis de Transact-SQL (Transact-SQL). Las funciones con valores de tabla se pueden invocar cuando se admiten expresiones de tabla en la cláusula FROM de instrucciones SELECT, INSERT, UPDATE o DELETE. Para obtener más información, vea Ejecutar funciones definidas por el usuario (motor de base de datos).
Usar parámetros y valores devueltos en funciones CLR
Si se especifican parámetros en una función CLR, deben ser de tipos de SQL Server tal como se definieron anteriormente para scalar_parameter_data_type. Para obtener información sobre la comparación de los tipos de datos del sistema de SQL Server con los tipos de datos de integración de CLR o los tipos de datos de .NET Framework Common Language Runtime, vea SQL Server Data Types and Their .NET Framework Equivalents.
Para que SQL Server haga referencia al método correcto cuando se sobrecarga en una clase, el método indicado en <method_specifier> debe tener las siguientes características:
-
Recibir el mismo número de parámetros que se especifica en [ ,...n ].
-
Recibir todos los parámetros por valor y no por referencia.
-
Utilizar tipos de parámetros que sean compatibles con los especificados en la función de SQL Server.
Si el tipo de datos de retorno de la función CLR especifica un tipo de tabla (RETURNS TABLE), el tipo de datos de retorno del método <method_specifier> debe ser IEnumerator o IEnumerable, y se da por supuesto que el creador de la función implementa la interfaz. A diferencia de las funciones Transact-SQL, las funciones CLR no pueden incluir restricciones PRIMARY KEY, UNIQUE o CHECK en <table_type_definition>. Los tipos de datos de las columnas especificadas en <table_type_definition> deben coincidir con los tipos de las columnas correspondientes del conjunto de resultados devuelto por el método en <method_specifier> en tiempo de ejecución. Esta comprobación del tipo no se lleva a cabo en el momento de crear la función.
Para obtener más información sobre la programación de funciones CLR, vea CLR User-Defined Functions.
Instrucciones SQL no permitidas
Las siguientes instrucciones de Service Broker no se pueden incluir en la definición de una función Transact-SQL definida por el usuario:
-
BEGIN DIALOG CONVERSATION
-
END CONVERSATION
-
GET CONVERSATION GROUP
-
MOVE CONVERSATION
-
RECEIVE
-
SEND
Ver información de las funciones
Para ver la definición de las funciones Transact-SQL definidas por el usuario, utilice la vista de catálogo sys.sql_modules en la base de datos en la que está la función.
Por ejemplo:
USE AdventureWorks;
GO
SELECT definition, type
FROM sys.sql_modules AS m
JOIN sys.objects AS o ON m.object_id = o.object_id
AND type IN ('FN', 'IF', 'TF');
GO
| Nota: |
|---|
|
La definición de funciones creadas con la opción ENCRYPTION no se puede ver con sys.sql_modules; sin embargo, se muestra otra información acerca de las funciones cifradas.
|
Para ver información acerca de las funciones definidas por el usuario CLR, utilice la vista de catálogo sys.assembly_modules en la base de datos en la que está la función.
Para ver información acerca de los parámetros definidos en las funciones definidas por el usuario, utilice la vista de catálogo sys.parameters en la base de datos en la que está la función.
Para obtener un informe de los objetos a los que hace referencia una función, utilice sys.sql_dependencies.
A. Usar una función con valores escalares definida por el usuario que calcula la semana ISO
En el ejemplo siguiente se crea la función definida por el usuario ISOweek. Esta función usa un argumento de fecha para calcular el número de semana ISO. Para que esta función calcule correctamente, se debe invocar SET DATEFIRST 1 antes de llamar a la función.
En el ejemplo también se muestra el uso de la cláusula EXECUTE AS para especificar el contexto de seguridad en el que se puede ejecutar un procedimiento almacenado. En el ejemplo, la opción CALLER especifica que el procedimiento se ejecutará en el contexto del usuario que lo llama. Las otras opciones que puede especificar son SELF, OWNER y user_name.
Ésta es la llamada a la función. Observe que el valor de DATEFIRST es 1.
USE AdventureWorks;
GO
IF OBJECT_ID (N'dbo.ISOweek', N'FN') IS NOT NULL
DROP FUNCTION dbo.ISOweek;
GO
CREATE FUNCTION dbo.ISOweek (@DATE datetime)
RETURNS int
WITH EXECUTE AS CALLER
AS
BEGIN
DECLARE @ISOweek int;
SET @ISOweek= DATEPART(wk,@DATE)+1
-DATEPART(wk,CAST(DATEPART(yy,@DATE) as CHAR(4))+'0104');
--Special cases: Jan 1-3 may belong to the previous year
IF (@ISOweek=0)
SET @ISOweek=dbo.ISOweek(CAST(DATEPART(yy,@DATE)-1
AS CHAR(4))+'12'+ CAST(24+DATEPART(DAY,@DATE) AS CHAR(2)))+1;
--Special case: Dec 29-31 may belong to the next year
IF ((DATEPART(mm,@DATE)=12) AND
((DATEPART(dd,@DATE)-DATEPART(dw,@DATE))>= 28))
SET @ISOweek=1;
RETURN(@ISOweek);
END;
GO
SET DATEFIRST 1;
SELECT dbo.ISOweek(CONVERT(DATETIME,'12/26/2004',101)) AS 'ISO Week';
Éste es el conjunto de resultados.
ISO Week
----------------
52
B. Crear una función con valores de tabla en línea
El siguiente ejemplo devuelve una función con valores de tabla en línea. Devuelve tres columnas ProductID, Name y el agregado de ventas totales anuales hasta la fecha por tienda como YTD Total para cada producto vendido a la tienda.
USE AdventureWorks;
GO
IF OBJECT_ID (N'Sales.ufn_SalesByStore', N'IF') IS NOT NULL
DROP FUNCTION Sales.ufn_SalesByStore;
GO
CREATE FUNCTION Sales.ufn_SalesByStore (@storeid int)
RETURNS TABLE
AS
RETURN
(
SELECT P.ProductID, P.Name, SUM(SD.LineTotal) AS 'YTD Total'
FROM Production.Product AS P
JOIN Sales.SalesOrderDetail AS SD ON SD.ProductID = P.ProductID
JOIN Sales.SalesOrderHeader AS SH ON SH.SalesOrderID = SD.SalesOrderID
WHERE SH.CustomerID = @storeid
GROUP BY P.ProductID, P.Name
);
GO
Para invocar la función, ejecute esta consulta.
SELECT * FROM Sales.ufn_SalesByStore (602);
C. Crear una función con valores de tabla de múltiples instrucciones
En el ejemplo siguiente se crea la función con valores de tabla fn_FindReports(InEmpID). Cuando se suministra un Id. de empleado válido, la función devuelve una tabla de todos los empleados que están bajo las órdenes de ese empleado tanto directa como indirectamente. La función utiliza la expresión de tabla común (CTE) recursiva para producir la lista jerárquica de empleados. Para obtener más información sobre las CTE recursivas, vea WITH common_table_expression (Transact-SQL).
USE AdventureWorks;
GO
IF OBJECT_ID (N'dbo.ufn_FindReports', N'TF') IS NOT NULL
DROP FUNCTION dbo.ufn_FindReports;
GO
CREATE FUNCTION dbo.ufn_FindReports (@InEmpID INTEGER)
RETURNS @retFindReports TABLE
(
EmployeeID int primary key NOT NULL,
Name nvarchar(255) NOT NULL,
Title nvarchar(50) NOT NULL,
EmployeeLevel int NOT NULL,
Sort nvarchar (255) NOT NULL
)
--Returns a result set that lists all the employees who report to the
--specific employee directly or indirectly.*/
AS
BEGIN
WITH DirectReports(Name, Title, EmployeeID, EmployeeLevel, Sort) AS
(SELECT CONVERT(Varchar(255), c.FirstName + ' ' + c.LastName),
e.Title,
e.EmployeeID,
1,
CONVERT(Varchar(255), c.FirstName + ' ' + c.LastName)
FROM HumanResources.Employee AS e
JOIN Person.Contact AS c ON e.ContactID = c.ContactID
WHERE e.EmployeeID = @InEmpID
UNION ALL
SELECT CONVERT(Varchar(255), REPLICATE ('| ' , EmployeeLevel) +
c.FirstName + ' ' + c.LastName),
e.Title,
e.EmployeeID,
EmployeeLevel + 1,
CONVERT (Varchar(255), RTRIM(Sort) + '| ' + FirstName + ' ' +
LastName)
FROM HumanResources.Employee as e
JOIN Person.Contact AS c ON e.ContactID = c.ContactID
JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID
)
-- copy the required columns to the result of the function
INSERT @retFindReports
SELECT EmployeeID, Name, Title, EmployeeLevel, Sort
FROM DirectReports
RETURN
END;
GO
-- Example invocation
SELECT EmployeeID, Name, Title, EmployeeLevel
FROM dbo.ufn_FindReports(109)
ORDER BY Sort;
GO
D. Crear una función CLR
En el siguiente ejemplo se supone que los ejemplos de motor de base de datos de SQL Server están instalados en la ubicación predeterminada del equipo local y que la aplicación de ejemplo StringManipulate.csproj está compilada. Para obtener más información, vea Manipulación de cadenas que detectan caracteres complementarios.
El ejemplo crea la función len_s de CLR. Antes de crear la función, el ensamblado SurrogateStringFunction.dll se registra en la base de datos local.
DECLARE @SamplesPath nvarchar(1024);
-- You may have to modify the value of the this variable if you have
--installed the sample someplace other than the default location.
SELECT @SamplesPath = REPLACE(physical_name, 'Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf', 'Microsoft SQL Server\90\Samples\Engine\Programmability\CLR\')
FROM master.sys.database_files
WHERE name = 'master';
CREATE ASSEMBLY [SurrogateStringFunction]
FROM @SamplesPath + 'StringManipulate\CS\StringManipulate\bin\debug\SurrogateStringFunction.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS;
GO
CREATE FUNCTION [dbo].[len_s] (@str nvarchar(4000))
RETURNS bigint
AS EXTERNAL NAME [SurrogateStringFunction].[Microsoft.Samples.SqlServer.SurrogateStringFunction].[LenS];
GO
Para ver un ejemplo de creación de una función CLR con valores de tabla, vea CLR Table-Valued Functions.