CREATE FUNCTION (Transact-SQL)

Sintaxis

--Transact-SQL Scalar Function Syntax
CREATE FUNCTION [ schema_name. ] function_name 
( [ { @parameter_name [ AS ][ type_schema_name. ] parameter_data_type 
    [ = default ] [ READONLY ] } 
    [ ,...n ]
  ]
)
RETURNS return_data_type
    [ WITH <function_option> [ ,...n ] ]
    [ AS ]
    BEGIN 
        function_body 
        RETURN scalar_expression
    END
[ ; ]

--Transact-SQL Inline Table-Valued Function Syntax 
CREATE FUNCTION [ schema_name. ] function_name 
( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type 
    [ = default ] [ READONLY ] } 
    [ ,...n ]
  ]
)
RETURNS TABLE
    [ WITH <function_option> [ ,...n ] ]
    [ AS ]
    RETURN [ ( ] select_stmt [ ) ]
[ ; ]

--Transact-SQL Multistatement Table-valued Function Syntax
CREATE FUNCTION [ schema_name. ] function_name 
( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type 
    [ = default ] [READONLY] } 
    [ ,...n ]
  ]
)
RETURNS @return_variable TABLE <table_type_definition>
    [ WITH <function_option> [ ,...n ] ]
    [ AS ]
    BEGIN 
        function_body 
        RETURN
    END
[ ; ]

--Transact-SQL Function Clauses 
<function_option>::= 
{
    [ ENCRYPTION ]
  | [ SCHEMABINDING ]
  | [ RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT ]
  | [ EXECUTE_AS_Clause ]
}

<table_type_definition>:: = 
( { <column_definition> <column_constraint> 
  | <computed_column_definition> } 
    [ <table_constraint> ] [ ,...n ]
) 

<column_definition>::=
{
    { column_name data_type }
    [ [ DEFAULT constant_expression ] 
      [ COLLATE collation_name ] | [ ROWGUIDCOL ]
    ]
    | [ IDENTITY [ (seed , increment ) ] ]
    [ <column_constraint> [ ...n ] ] 
}

<column_constraint>::= 
{
    [ NULL | NOT NULL ] 
    { PRIMARY KEY | UNIQUE }
      [ CLUSTERED | NONCLUSTERED ] 
      [ WITH FILLFACTOR = fillfactor 
        | WITH ( < index_option > [ , ...n ] )
      [ ON { filegroup | "default" } ]
  | [ CHECK ( logical_expression ) ] [ ,...n ]
}

<computed_column_definition>::=
column_name AS computed_column_expression 

<table_constraint>::=
{ 
    { PRIMARY KEY | UNIQUE }
      [ CLUSTERED | NONCLUSTERED ] 
      ( column_name [ ASC | DESC ] [ ,...n ] )
        [ WITH FILLFACTOR = fillfactor 
        | WITH ( <index_option> [ , ...n ] )
  | [ CHECK ( logical_expression ) ] [ ,...n ]
}

<index_option>::=
{ 
    PAD_INDEX = { ON | OFF } 
  | FILLFACTOR = fillfactor 
  | IGNORE_DUP_KEY = { ON | OFF }
  | STATISTICS_NORECOMPUTE = { ON | OFF } 
  | ALLOW_ROW_LOCKS = { ON | OFF }
  | ALLOW_PAGE_LOCKS ={ ON | OFF } 
}

--CLR Scalar Function Syntax
CREATE FUNCTION [ schema_name. ] function_name 
( { @parameter_name [AS] [ type_schema_name. ] parameter_data_type 
    [ = default ] } 
    [ ,...n ]
)
RETURNS { return_data_type }
    [ WITH <clr_function_option> [ ,...n ] ]
    [ AS ] EXTERNAL NAME <method_specifier>
[ ; ]

--CLR Table-Valued Function Syntax
CREATE FUNCTION [ schema_name. ] function_name 
( { @parameter_name [AS] [ type_schema_name. ] parameter_data_type 
    [ = default ] } 
    [ ,...n ]
)
RETURNS TABLE <clr_table_type_definition> 
    [ WITH <clr_function_option> [ ,...n ] ]
    [ ORDER ( <order_clause> ) ]
    [ AS ] EXTERNAL NAME <method_specifier>
[ ; ]

--CLR Function Clauses
<order_clause> ::= 
{
   <column_name_in_clr_table_type_definition>
   [ ASC | DESC ] 
} [ ,...n] 

<method_specifier>::=
    assembly_name.class_name.method_name

<clr_function_option>::=
}
    [ RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT ]
  | [ EXECUTE_AS_Clause ]
}

<clr_table_type_definition>::= 
( { column_name data_type } [ ,...n ] )

Argumentos

• schema_name
  Nombre del esquema al que pertenece la función definida por el usuario.

• function_name
  Nombre de la función definida por el usuario. Los nombres de funciones deben seguir las reglas de los identificadores y deben ser únicos en la base de datos y para su esquema.

  Nota

  Los paréntesis después del nombre de la función son necesarios, aunque no se especifique un parámetro.

• @parameter_name
  Es un parámetro de la función definida por el usuario. Es posible declarar uno o varios parámetros.

  Una función puede tener un máximo de 2.100 parámetros. El usuario debe proporcionar el valor de cada parámetro declarado cuando se ejecuta la función, a menos que se defina un valor predeterminado para el parámetro.

  Especifique un nombre de parámetro con una arroba (@) como primer carácter. El nombre del parámetro debe cumplir las mismas reglas que los identificadores. Los parámetros son locales para la función; los mismos nombres de parámetro se pueden usar en otras funciones. Los parámetros solamente pueden ocupar el lugar de constantes; no se pueden utilizar en lugar de nombres de tablas, nombres de columnas o nombres de otros objetos de base de datos.

  Nota

  ANSI_WARNINGS no se respeta al pasar parámetros en un procedimiento almacenado o una función definida por el usuario, ni cuando se declaran y se establecen variables en una instrucción por lotes. Por ejemplo, si una variable se define como char(3) y después se establece en un valor de más de tres caracteres, los datos se truncan hasta reducirse al tamaño definido y la instrucción INSERT o UPDATE se ejecuta correctamente.

• [ type_schema_name. ] parameter_data_type
  Es el tipo de datos del parámetro y, de forma opcional, el esquema al que pertenece. Para las funciones Transact-SQL, se permiten todos los tipos de datos, incluidos los tipos CLR definidos por el usuario y los tipos de tablas definidos por el usuario, a excepción del tipo de datos timestamp. Para las funciones CLR se admiten todos los tipos de datos, incluidos los tipos CLR definidos por el usuario, excepto text, ntext, image, los tipos de tablas definidos por el usuario y los tipos de datos timestamp. Los tipos de datos no escalares, cursor y table, no se pueden especificar como tipos de datos de parámetro en funciones Transact-SQL ni en funciones CLR.

  Si no se especifica type_schema_name, Motor de base de datos busca scalar_parameter_data_type en el siguiente orden:

  • El esquema que contiene los nombres de los tipos de datos del sistema de SQL Server.

  • El esquema predeterminado del usuario actual de la base de datos actual.

  • El esquema de dbo debe existir en la base de datos actual.

• [ =default ]
  Es un valor predeterminado para el parámetro. Si se define un valor default, la función se puede ejecutar sin especificar un valor para ese parámetro.

  Nota

  Se pueden especificar valores predeterminados de parámetros para las funciones CLR, excepto para los tipos de datos varchar(max) y varbinary(max).

  Cuando un parámetro de la función tiene un valor predeterminado, se debe especificar la palabra clave DEFAULT cuando se llama a la función para recuperar el valor predeterminado. Este comportamiento es distinto del uso de parámetros con valores predeterminados en los procedimientos almacenados donde la omisión del parámetro implica también especificar el valor predeterminado. Una excepción a este comportamiento es cuando se invoca una función escalar utilizando la instrucción EXECUTE. Cuando se usa EXECUTE no se requiere la palabra clave DEFAULT.

• READONLY
  Indica que el parámetro no se puede actualizar ni modificar en la definición de la función. Si el tipo de parámetro es un tipo de tabla definido por el usuario, se debe especificar READONLY.

• return_data_type
  Es el valor devuelto de una función escalar definida por el usuario. Para las funciones Transact-SQL, se permiten todos los tipos de datos, incluidos los tipos CLR definidos por el usuario, a excepción del tipo de datos timestamp. Para las funciones CLR, se permiten todos los tipos de datos, incluidos los tipos CLR definidos por el usuario, a excepción de los tipos de datos text, ntext, image y timestamp. Los tipos de datos no escalares, cursor y table, no se pueden especificar como tipos de datos devueltos en funciones Transact-SQL o CLR. 

• function_body
  Especifica que una serie de instrucciones Transact-SQL, que juntas no producen ningún efecto secundario (como, por ejemplo, modificar una tabla), definen el valor de la función. function_body solamente se usa en funciones escalares y funciones con valores de tabla de múltiples instrucciones.

  En las funciones escalares, function_body es una serie de instrucciones Transact-SQL que juntas se evalúan como un valor escalar.

  En las funciones con valores de tabla de múltiples instrucciones, function_body es una serie de instrucciones Transact-SQL que rellenan una variable devuelta de TABLE.

• scalar_expression
  Especifica el valor escalar que devuelve la función escalar.

• TABLE
  Especifica que el valor devuelto de la función con valores de tabla es una tabla. Solamente se pueden pasar constantes y @local_variables a las funciones con valores de tabla.

  En las funciones insertadas con valores de tabla, el valor devuelto de TABLE se define mediante una única instrucción SELECT. Las funciones insertadas no tienen variables devueltas asociadas.

  En las funciones con valores de tabla de múltiples instrucciones, @return_variable es una variable de TABLE, que se utiliza para almacenar y acumular las filas que se deben devolver como valor de la función. @return_variable solamente se puede especificar para funciones Transact-SQL, no para funciones CLR.

  Advertencia

  Se permite la unión a una función con valores de tabla de múltiples instrucciones en una cláusula FROM, pero el rendimiento puede ser deficiente. SQL Server no puede usar todas las técnicas optimizadas en algunas instrucciones que se pueden incluir en una función con múltiples instrucciones, lo que puede dar lugar a un plan de consulta poco óptimo. Para obtener el mejor rendimiento posible, siempre que sea posible, utilice combinaciones entre tablas base en lugar de funciones.

• select_stmt
  Es la instrucción SELECT individual que define el valor devuelto de una función insertada con valores de tabla.

• ORDER (<order_clause>)
  Especifica el orden en que los resultados se devuelven de la función con valores de tabla. Para obtener más información, vea la sección "Instrucciones para usar el criterio de ordenación" más adelante en este tema.

• EXTERNAL NAME <method_specifier> assembly_name.class_name.method_name
  Especifica el método de ensamblado para enlazar con la función. assembly_name debe coincidir con un ensamblado existente en SQL Server, en la base de datos actual con la visibilidad activada. class_name debe ser un identificador SQL Server válido y debe existir como clase en el ensamblado. Si la clase tiene un nombre calificado como espacio de nombres con un punto (.) para separar las partes del espacio de nombres, se debe delimitar el nombre de la clase con corchetes ([ ]) o comillas (" "). method_name debe ser un identificador de SQL Server válido y debe existir como método estático en la clase especificada.

  Nota

  De manera predeterminada, SQL Server no puede ejecutar código CLR. Puede crear, modificar y quitar objetos de base de datos que hagan referencia a módulos de Common Language Runtime; sin embargo, no puede ejecutar estas referencias en SQL Server si no habilita la opción clr enabled. Para habilitar esta opción, utilice sp_configure.

• <table_type_definition> ( { <column_definition> <column_constraint>   | <computed_column_definition> }   [ <table_constraint> ] [ ,...n ] )
  Define el tipo de datos de tabla para una función Transact-SQL. La declaración de tabla incluye definiciones de columna y restricciones de columna o de tabla. La tabla se coloca siempre en el grupo de archivos principal.

• < definición_de_tipo_de_tabla_clr > ( { column_namedata_type } [ ,...n ] )
  Define los tipos de datos de tabla para una función CLR. La declaración de tabla solamente incluye nombres de columna y tipos de datos. La tabla se coloca siempre en el grupo de archivos principal.

Prácticas recomendadas

Si una función definida por el usuario no se crea con la cláusula SCHEMABINDING, los cambios que se realicen en los objetos subyacentes pueden afectar a la definición de la función y generar resultados inesperados al invocarla. Recomendamos implementar uno de los siguientes métodos para garantizar que la función no queda sin actualizar como consecuencia de los cambios realizados en sus objetos subyacentes:

• Especifique la cláusula WITH SCHEMABINDING al crear la función. Así se asegura de que no se pueden modificar los objetos a los que se hace referencia en la definición de la función a menos que también se modifique la función.

• Ejecute el procedimiento almacenado sp_refreshsqlmodule después de modificar cualquier objeto que se especifique en la definición de la función.

Tipos de datos

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 Asignar datos de parámetros CLR.

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 devuelto de la función CLR especifica un tipo de tabla (RETURNS TABLE), el tipo de datos devuelto del método de <method_specifier> debe ser de tipo 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 Funciones CLR definidas por el usuario.

Comentarios generales

Se pueden invocar funciones escalares cuando se utilizan expresiones escalares. Esto incluye las columnas calculadas y las definiciones de restricciones CHECK. Las funciones 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).

Interoperabilidad

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. Solamente 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 datos al cliente.

• Instrucciones INSERT, UPDATE y DELETE que modifican variables de tabla 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).

Interoperabilidad de columna calculada

Llamar a procedimientos almacenados extendidos desde funciones

Limitaciones y restricciones

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 no pueden tener una cláusula OUTPUT INTO que tiene una tabla como destino.

Las siguientes instrucciones de Service Broker no se pueden incluir en la definición de una función Transact-SQL definida por el usuario:

• EMPEZAR CONVERSACIÓN DE DIÁLOGO

• FINALIZAR CONVERSACIÓN

• OBTENER GRUPO DE CONVERSACIÓN

• MOVER CONVERSACIÓN

• RECIBIR

• ENVIAR

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. 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.

Usar el criterio de ordenación en funciones de tabla con valores de CLR

Metadatos

En la siguiente tabla se enumeran las vistas de catálogo del sistema que puede utilizar para devolver metadatos sobre las funciones definidas por el usuario.

USE AdventureWorks2008R2;
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

Permisos

Se requiere el permiso CREATE FUNCTION en la base de datos y el permiso ALTER en el esquema en el que se va a crear la función. Si la función especifica un tipo definido por el usuario, requiere el permiso EXECUTE para ese tipo.

Ejemplos

A. Usar una función con valores escalares definida por el usuario que calcula la semana ISO

USE AdventureWorks2008R2;
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';

B. Crear una función insertada con valores de tabla

USE AdventureWorks2008R2;
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 '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
    JOIN Sales.Customer AS C ON SH.CustomerID = C.CustomerID
    WHERE C.StoreID = @storeid
    GROUP BY P.ProductID, P.Name
);
GO

SELECT * FROM Sales.ufn_SalesByStore (602);

C. Crear una función con valores de tabla de múltiples instrucciones

USE AdventureWorks2008R2;
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,
    FirstName nvarchar(255) NOT NULL,
    LastName nvarchar(255) NOT NULL,
    JobTitle nvarchar(50) NOT NULL,
    RecursionLevel int NOT NULL
)
--Returns a result set that lists all the employees who report to the 
--specific employee directly or indirectly.*/
AS
BEGIN
WITH EMP_cte(EmployeeID, OrganizationNode, FirstName, LastName, JobTitle, RecursionLevel) -- CTE name and columns
    AS (
        SELECT e.BusinessEntityID, e.OrganizationNode, p.FirstName, p.LastName, e.JobTitle, 0 -- Get the initial list of Employees for Manager n
        FROM HumanResources.Employee e 
            INNER JOIN Person.Person p 
            ON p.BusinessEntityID = e.BusinessEntityID
        WHERE e.BusinessEntityID = @InEmpID
        UNION ALL
        SELECT e.BusinessEntityID, e.OrganizationNode, p.FirstName, p.LastName, e.JobTitle, RecursionLevel + 1 -- Join recursive member to anchor
        FROM HumanResources.Employee e 
            INNER JOIN EMP_cte
            ON e.OrganizationNode.GetAncestor(1) = EMP_cte.OrganizationNode
            INNER JOIN Person.Person p 
            ON p.BusinessEntityID = e.BusinessEntityID
        )
-- copy the required columns to the result of the function 
   INSERT @retFindReports
   SELECT EmployeeID, FirstName, LastName, JobTitle, RecursionLevel
   FROM EMP_cte 
   RETURN
END;
GO
-- Example invocation
SELECT EmployeeID, FirstName, LastName, JobTitle, RecursionLevel
FROM dbo.ufn_FindReports(1); 

GO

D. Crear una función CLR

DECLARE @SamplesPath nvarchar(1024);
-- You may have to modify the value of this variable if you have
-- installed the sample in a location other than the default location.
SELECT @SamplesPath = REPLACE(physical_name, 'Microsoft SQL Server\MSSQL10_5.MSSQLSERVER\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

Vea también

Referencia

Otros recursos

Historial de cambios