CREATE FUNCTION (Transact-SQL)

Crea una función definida por el usuario en SQL Server 2012. Una función definida por el usuario es una rutina de Transact-SQL o Common Language Runtime (CLR) que acepta parámetros, realiza una acción, como un cálculo complejo, y devuelve el resultado de esa acción como un valor. El valor devuelto puede ser un valor escalar (único) o una tabla. Utilice esta instrucción para crear una rutina reutilizable que se pueda utilizar de estas formas:

• En instrucciones Transact-SQL como SELECT

• En las aplicaciones que llaman a la función

• En la definición de otra función definida por el usuario

• Para parametrizar una vista o mejorar la funcionalidad de una vista indizada

• Para definir una columna en una tabla

• Para definir una restricción CHECK en una columna

• Para reemplazar un procedimiento almacenado

Convenciones de sintaxis de 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 utilizar 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 al llamar 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 especificar el valor predeterminado. Sin embargo, la palabra clave DEFAULT no se requiere cuando se invoca una función escalar mediante la instrucción EXECUTE.

• 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 modificar una tabla), definen el valor de la función. function_body solamente se usa en funciones escalares y en 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

  Es posible realizar combinaciones en una función con valores de tabla de múltiples instrucciones en una cláusula FROM, pero puede provocar una degradación en el rendimiento. SQL Server no puede usar todas las técnicas optimizadas frente a ciertas instrucciones que se pueden incluir en una función de múltiples instrucciones, ya que conllevaría que el plan de consulta no alcanzase un nivel óptimo. Para lograr el mejor rendimiento, siempre que sea posible, use combinaciones entre las 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 un ensamblado que se enlaza a 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 completo de espacio de nombres que usa un punto (.) para separar las partes del espacio de nombres, el nombre de la clase debe delimitarse mediante corchetes ([ ]) o comillas (" "). method_name debe ser un identificador 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. Se pueden crear, modificar y quitar objetos de bases de datos que hagan referencia a módulos de Common Language Runtime; sin embargo, estas referencias no se pueden ejecutar en SQL Server hasta que se habilite la opción clr enabled. Para habilitar esta opción, utilice sp_configure.

  [!NOTA]

  Esta opción no está disponible en una base de datos contenida.

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

<opción_de_función>::= y <opción_de_función_clr>::=

Especifica que la función tendrá una o más de las siguientes opciones.

• ENCRYPTION
  Indica que Motor de base de datos convertirá el texto original de la instrucción CREATE FUNCTION a un formato confuso. La salida de los datos confusos no es directamente visible en ninguna de las vistas de catálogo. Los usuarios que no disponen de acceso a las tablas del sistema o a los archivos de base de datos no pueden recuperar el texto protegido. Sin embargo, estará disponible para los usuarios con privilegios que puedan obtener acceso a las tablas del sistema a través del puerto DAC o directamente a los archivos de base de datos. Además, los usuarios que pueden adjuntar un depurador al proceso del servidor pueden recuperar el procedimiento original de la memoria en tiempo de ejecución. Para obtener más información acerca del acceso a los metadatos del sistema, vea Configuración de visibilidad de los metadatos.

  El uso de esta opción impide que la función se publique como parte de la replicación de SQL Server. Esta opción no se puede especificar para funciones CLR.

• SCHEMABINDING
  Especifica que la función está enlazada a los objetos de base de datos a los que hace referencia. Cuando se especifica SCHEMABINDING, los objetos base no se pueden modificar de una forma que afecte a la definición de la función. En primer lugar, se debe modificar o quitar la propia definición de la función para quitar las dependencias en el objeto que se va a modificar.

  El enlace de la función a los objetos a los que hace referencia solamente se quita cuando se ejecuta una de estas acciones:

  • Se quita la función.

  • La función se modifica con la instrucción ALTER sin especificar la opción SCHEMABINDING.

  Una función se puede enlazar a esquema solamente si se cumplen las siguientes condiciones:

  • La función es una función de Transact-SQL.

  • Las funciones definidas por el usuario y las vistas a las que hace referencia la función también están enlazadas al esquema.

  • La función hace referencia a los objetos utilizando un nombre en dos partes.

  • La función y los objetos a los que hace referencia pertenecen a la misma base de datos.

  • El usuario que ejecutó la instrucción CREATE FUNCTION tiene permisos REFERENCES para los objetos de base de datos a los que hace referencia la función.

• RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT
  Especifica el atributo OnNULLCall de una función escalar. Si no se especifica, CALLED ON NULL INPUT está implícito de manera predeterminada. Esto significa que el cuerpo de la función se ejecuta aunque se envíe NULL como argumento.

  Si se especifica RETURNS NULL ON NULL INPUT en una función CLR, esto indica que SQL Server puede devolver NULL cuando cualquiera de los argumentos que recibe sea NULL, sin invocar realmente el cuerpo de la función. Si el método de una función CLR especificado en <method_specifier> ya tiene un atributo personalizado que indica RETURNS NULL ON NULL INPUT, pero la instrucción CREATE FUNCTION indica CALLED ON NULL INPUT, la instrucción CREATE FUNCTION tiene prioridad. El atributo OnNULLCall no se puede especificar para las funciones CLR con valores de tabla.

• Cláusula EXECUTE AS
  Especifica el contexto de seguridad en el que se ejecuta la función definida por el usuario. Por lo tanto, es posible controlar la cuenta de usuario que utiliza SQL Server para validar los permisos en los objetos de base de datos a los que hace referencia la función.

  [!NOTA]

  EXECUTE AS no se puede especificar para las funciones insertadas definidas por el usuario.

  Para obtener más información, vea EXECUTE AS (cláusula de Transact-SQL).

< column_definition >::=

Define el tipo de datos de tabla. La declaración de tabla incluye definiciones de columna y restricciones. Para las funciones CLR, solamente se puede especificar column_name y data_type.

• column_name
  Es el nombre de una columna de la tabla. Los nombres de columna se deben ajustar a las reglas para los identificadores y deben ser únicos en la tabla. column_name puede tener de 1 a 128 caracteres.

• data_type
  Especifica el tipo de datos de la columna. Para las funciones Transact-SQL, se permiten todos los tipos de datos, incluidos los tipos CLR definidos por el usuario, a excepción de timestamp. Para las funciones CLR, se permiten todos los tipos de datos, incluidos los tipos CLR definidos por el usuario, a excepción de text, ntext, image, char, varchar, varchar(max) y timestamp. El tipo no escalar cursor no se puede especificar como tipo de datos de columna en funciones Transact-SQL o CLR.

• DEFAULT constant_expression
  Especifica el valor suministrado para la columna cuando no se ha proporcionado explícitamente un valor durante la inserción. constant_expression es una constante, un valor NULL o un valor de función del sistema. Se pueden aplicar definiciones con el valor DEFAULT a cualquier columna, excepto las que incluyen la propiedad IDENTITY. No se puede especificar DEFAULT para las funciones CLR con valores de tabla.

• COLLATE collation_name
  Especifica la intercalación de la columna. Si no se especifica, se asigna a la columna la intercalación predeterminada de la base de datos. El nombre de intercalación puede ser un nombre de intercalación de Windows o un nombre de intercalación de SQL. Para obtener más información y una lista de nombres de intercalación, vea Nombre de intercalación de Windows (Transact-SQL) y Nombre de intercalación de SQL Server (Transact-SQL).

  Es posible utilizar la cláusula COLLATE solo para cambiar las intercalaciones de las columnas cuyo tipo de datos sea char, varchar, nchar y nvarchar.

  No se puede especificar el valor COLLATE para las funciones CLR con valores de tabla.

• ROWGUIDCOL
  Indica que la nueva columna es una columna de identificador único global de fila. Solo se puede designar una columna uniqueidentifier por tabla como columna ROWGUIDCOL. La propiedad ROWGUIDCOL únicamente se puede asignar a una columna uniqueidentifier.

  La propiedad ROWGUIDCOL no exige que los valores almacenados en la columna sean únicos. Del mismo modo, tampoco genera automáticamente valores para nuevas filas insertadas en la tabla. Si desea generar valores únicos para cada columna, use la función NEWID en instrucciones INSERT. Puede especificar un valor predeterminado; sin embargo, no puede especificar NEWID como valor predeterminado.

• IDENTITY
  Indica que la nueva columna es una columna de identidad. Cuando se agrega una nueva fila a la tabla, SQL Server proporciona un valor incremental único para la columna. Las columnas de identidad se utilizan normalmente junto con las restricciones PRIMARY KEY como identificadores de fila exclusivos de la tabla. La propiedad IDENTITY se puede asignar a columnas tinyint, smallint, int, bigint, decimal(p,0) o numeric(p,0). Solo se puede crear una columna de identidad para cada tabla. Las restricciones DEFAULT y los valores predeterminados enlazados no se pueden utilizar en las columnas de identidad. Debe especificar tanto el valor de seed como de increment, o bien ninguno de los dos. Si no se especifica ninguno, el valor predeterminado es (1,1).

  No se puede especificar IDENTITY para las funciones CLR con valores de tabla.

  • seed
    Es el valor entero que se asignará a la primera fila de la tabla.

  • increment
    Es el valor entero que se va a agregar al valor de seed en las sucesivas filas de la tabla.

< column_constraint >::= y < table_constraint>::=

Define la restricción para la columna o tabla especificada. Para las funciones CLR, el único tipo de restricción permitido es NULL. No se permiten las restricciones con nombre.

• NULL | NOT NULL
  Determina si se permiten valores NULL en la columna. NULL no es estrictamente una restricción, pero se puede especificar, al igual que NOT NULL. No se puede especificar NOT NULL para las funciones CLR con valores de tabla.

• PRIMARY KEY
  Es una restricción que exige la integridad de entidad para la columna especificada a través de un índice único. En las funciones con valores de tabla definidas por el usuario, la restricción PRIMARY KEY solamente se puede crear en una columna de cada tabla. No se puede especificar PRIMARY KEY para las funciones CLR con valores de tabla.

• UNIQUE
  Es una restricción que proporciona la integridad de entidad para una o varias columnas especificadas a través de un índice único. Las tablas pueden tener varias restricciones UNIQUE. No se puede especificar UNIQUE para las funciones CLR con valores de tabla.

• CLUSTERED | NONCLUSTERED
  Indica que se ha creado un índice clúster o no clúster para la restricción PRIMARY KEY o UNIQUE. Las restricciones PRIMARY KEY utilizan CLUSTERED y las restricciones UNIQUE, NONCLUSTERED.

  CLUSTERED solamente se puede especificar para una restricción. Si se especifica CLUSTERED para una restricción UNIQUE y también se especifica una restricción PRIMARY KEY, esta última utilizará NONCLUSTERED.

  No se pueden especificar CLUSTERED y NONCLUSTERED para las funciones CLR con valores de tabla.

• CHECK
  Es una restricción que exige la integridad del dominio al limitar los valores posibles que se pueden escribir en una o varias columnas. No se pueden especificar restricciones CHECK para las funciones CLR con valores de tabla.

  • logical_expression
    Es una expresión lógica que devuelve TRUE o FALSE.

<computed_column_definition>::=

Especifica una columna calculada. Para obtener más información sobre las columnas calculadas, vea CREATE TABLE (Transact-SQL).

• column_name
  Es el nombre de la columna calculada.

• computed_column_expression
  Es una expresión que define el valor de una columna calculada.

<index_option>::=

Especifica las opciones de índice para el índice PRIMARY KEY o UNIQUE. Para obtener más información sobre las opciones de índice, vea CREATE INDEX (Transact-SQL).

• PAD_INDEX = { ON | OFF }
  Especifica el relleno del índice. El valor predeterminado es OFF.

• FILLFACTOR = fillfactor
  Especifica un porcentaje que indica cuánto debe llenar el Motor de base de datos el nivel hoja de cada página de índice durante la creación o modificación de los índices. fillfactor debe ser un valor entero comprendido entre 1 y 100. El valor predeterminado es 0.

• IGNORE_DUP_KEY = { ON | OFF }
  Especifica la respuesta de error cuando una operación de inserción intenta insertar valores de clave duplicados en un índice único. La opción IGNORE_DUP_KEY se aplica solamente a operaciones de inserción realizadas tras crear o volver a generar el índice. El valor predeterminado es OFF.

• STATISTICS_NORECOMPUTE = { ON | OFF }
  Especifica si se vuelven a calcular las estadísticas de distribución. El valor predeterminado es OFF.

• ALLOW_ROW_LOCKS = { ON | OFF }
  Especifica si se admiten los bloqueos de fila. El valor predeterminado es ON.

• ALLOW_PAGE_LOCKS = { ON | OFF }
  Especifica si se permiten bloqueos de página. El valor predeterminado es ON.

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

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

En SQL Server 2005 y versiones posteriores, 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	Comentarios
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 contienen 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 las 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. El usuario puede especificar las propiedades de acceso a datos y determinismo de las funciones CLR. Para obtener más información, vea Información general de los atributos personalizados de la integración CLR.

Se deben crear las funciones de manera que el enlace de esquema sea determinista.

Para ver los valores actuales de estas propiedades, utilice OBJECTPROPERTYEX.

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

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 CLR con valores de tabla

Cuando utilice la cláusula ORDER en funciones CLR con valores de tabla, siga estas instrucciones:

• Debe asegurarse de que los resultados siempre se ordenan según el criterio especificado. Si los resultados no están en el orden especificado, SQL Server generará un mensaje de error cuando se ejecute la consulta.

• Si se especifica una cláusula ORDER, el resultado de la función con valores de tabla debe estar ordenado según la intercalación de la columna (explícita o implícita). Por ejemplo, si la intercalación de la columna es chino (especificado en el archivo DDL para la función con valores de tabla u obtenido a partir de la intercalación de la base de datos), los resultados devueltos deben estar ordenados según las reglas de ordenación chinas.

• SQL Server siempre comprueba la cláusula ORDER, si se especifica, durante la devolución de resultados, independientemente de si el procesador de consultas la utiliza para realizar más optimizaciones o no. Utilice la cláusula ORDER solamente si sabe que será útil para el procesador de consultas.

• El procesador de consultas de SQL Server utiliza de forma automática la cláusula ORDER en los siguientes casos:

  • Consultas Insert en las que la cláusula ORDER es compatible con un índice.

  • Cláusulas ORDER BY que son compatibles con la cláusula ORDER.

  • Agregados, donde GROUP BY es compatible con la cláusula ORDER.

  • Agregados DISTINCT donde las columnas distintas son compatibles con la cláusula ORDER.

La cláusula ORDER no garantiza que los resultados estarán ordenados al ejecutar una consulta SELECT, a menos que también se especifique la cláusula ORDER BY en la consulta. Vea sys.function_order_columns (Transact-SQL) para obtener información sobre cómo consultar las columnas incluidas en el criterio de ordenación para las funciones con valores de tabla.

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.

Vista del sistema	Descripción
sys.sql_modules	Muestra la definición de funciones definidas por el usuario de Transact-SQL. Por ejemplo: USE AdventureWorks2012; 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 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.
sys.assembly_modules	Muestra información sobre funciones definidas por el usuario CLR.
sys.parameters	Muestra información sobre los parámetros definidos en funciones definidas por el usuario.
sys.sql_expression_dependencies	Muestra los objetos subyacentes a los que hace referencia una función.

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

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.

Esta es la llamada a la función. Observe que el valor de DATEFIRST es 1.

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

El conjunto de resultados es el siguiente.

ISO Week

----------------

52

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

El siguiente ejemplo devuelve una función insertada con valores de tabla. 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 AdventureWorks2012;
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

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 identificador 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 AdventureWorks2012;
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

En el ejemplo se 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 this variable if you have
-- installed the sample in a location other than the default location.
SELECT @SamplesPath = REPLACE(physical_name, 'Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\master.mdf', 'Microsoft SQL Server\100\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 consultar un ejemplo de creación de una función CLR con valores de tabla, vea Funciones con valores de tabla en CLR.

Vea también

Referencia

ALTER FUNCTION (Transact-SQL)

DROP FUNCTION (Transact-SQL)

OBJECTPROPERTYEX (Transact-SQL)

sys.sql_modules (Transact-SQL)

sys.assembly_modules (Transact-SQL)

EXECUTE (Transact-SQL)

EVENTDATA (Transact-SQL)

Otros recursos

Funciones CLR definidas por el usuario