CREATE FUNCTION (Transact-SQL)

  • Artículo

Actualizado: 14 de abril de 2006

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 se modifican con ALTER FUNCTION y se quitan con DROP FUNCTION.

Icono de vínculo a temasConvenciones de sintaxis de Transact-SQL

Sintaxis

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

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

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

CLR Functions
CREATE FUNCTION [ schema_name. ] function_name 
( { @parameter_name [AS] [ type_schema_name. ] parameter_data_type 
        [ = default ] } 
    [ ,...n ]
)
RETURNS { return_data_type | TABLE <clr_table_type_definition> }
    [ WITH <clr_function_option> [ ,...n ] ]
    [ AS ] EXTERNAL NAME <method_specifier>
[ ; ]

Method Specifier
<method_specifier>::=
    assembly_name.class_name.method_name

Function Options
<function_option>::= 
{
    [ ENCRYPTION ]
  | [ SCHEMABINDING ]
  | [ RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT ]
  | [ EXECUTE_AS_Clause ]
}

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

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

<clr_table_type_definition>::= 
( { column_name data_type } [ ,...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 } 
}

Argumentos

  • schema_name
    El 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 1.024 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 sólo 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 la variable se define como char(3) y después se establece en un valor de más de tres caracteres, los datos se truncan con el 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 definidos por el usuario CLR, a excepción del tipo de datos timestamp. Para las funciones CLR, se permiten todos los tipos de datos, incluidos los tipos definidos por el usuario CLR, 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 de parámetro en funciones Transact-SQL o CLR.

    Si no se especifica type_schema_name, el SQL Server 2005 Database Engine (Motor de base de datos de SQL Server 2005) 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 en la base de datos actual.
    • El esquema dbo 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.

  • 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 definidos por el usuario CLR, a excepción del tipo de datos timestamp. Para las funciones CLR, se permiten todos los tipos de datos, incluidos los tipos definidos por el usuario CLR, 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 de retorno 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 sólo se utiliza 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 de retorno 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. Sólo se pueden pasar constantes y **@**local_variables a las funciones con valores de tabla.

    En las funciones con valores de tabla en línea, el valor devuelto de TABLE se define mediante una única instrucción SELECT. Las funciones en línea no tienen variables de retorno 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 sólo se puede especificar para funciones Transact-SQL, no para funciones CLR.

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

  • 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 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.
  • < clr_table_type_definition > , ( { column_namedata_type } [ ,...n ] ),
    Define los tipos de datos de tabla para una función CLR. La declaración de tabla sólo incluye nombres de columna y tipos de datos. La tabla se coloca siempre en el grupo de archivos principal.

<function_option>::= y <clr_function_option>::=

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

  • ENCRYPTION
    Indica que Database Engine (Motor de base de datos) convertirá el texto original de la instrucción CREATE FUNCTION a un formato ofuscado. La salida de la protección no es directamente visible en ninguna de las vistas de catálogo de SQL Server 2005. 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 réplica 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. Esta condición impide que se realicen cambios en la función si otros objetos enlazados del esquema hacen referencia a ella.

    El enlace de la función a los objetos a los que hace referencia sólo se elimina 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 sólo 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.

    No se puede especificar SCHEMABINDING para funciones CLR ni para funciones que hacen referencia a tipos de datos de alias.

  • RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT
    Especifica el atributo OnNULLCall de una función con valores escalares. Si no se especifica, se utiliza CALLED ON NULL INPUT 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, 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 con valores de tabla CLR.

  • 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 en línea 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, sólo se puede especificar column_name y data_type.

  • column_name
    Es el nombre de una columna de la tabla. Los nombres de columna deben cumplir las mismas reglas que los identificadores y 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 definidos por el usuario CLR, a excepción de timestamp. Para las funciones CLR, se permiten todos los tipos de datos, incluidos los tipos definidos por el usuario CLR, 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 proporcionado para la columna cuando no se suministra explícitamente un valor durante una 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 el valor DEFAULT para las funciones con valores de tabla CLR.

  • 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 (Transact-SQL).

    Es posible utilizar la cláusula COLLATE sólo 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 con valores de tabla CLR.

  • ROWGUIDCOL
    Indica que la nueva columna es una columna de identificador único global de fila. Sólo 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 impone la unicidad de los valores almacenados en la columna. 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). Sólo 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 el valor IDENTITY para las funciones con valores de tabla CLR.

    • seed
      Es el valor entero que se asignará a la primera fila de la tabla.
    • increment
      Se trata del incremento que se debe 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 el valor NOT NULL para las funciones con valores de tabla CLR.
  • 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 sólo se puede crear en una columna de cada tabla. No se puede especificar PRIMARY KEY para las funciones con valores de tabla CLR.
  • 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 múltiples restricciones UNIQUE. No se puede especificar el valor UNIQUE para las funciones con valores de tabla CLR.

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

    CLUSTERED sólo 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, ésta última utilizará NONCLUSTERED.

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

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

    • 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 Database Engine (Motor de base de datos) el nivel hoja de cada página del índice durante la creación o modificación de un índice. fillfactor debe ser un valor entero entre 1 y 100. El valor predeterminado es 0.
  • IGNORE_DUP_KEY = { ON | OFF }
    Especifica la respuesta de error a los valores de clave duplicados en una transacción de inserción de varias filas en un índice único agrupado o no agrupado. 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 admiten los bloqueos de página. El valor predeterminado es ON.

Notas

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.

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

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.

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

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 definidas por el usuario (motor de base de datos)
CLR User-Defined Functions

Ayuda e información

Obtener ayuda sobre SQL Server 2005

Historial de cambios

Versión Historial

14 de abril de 2006
Contenido nuevo:
  • En el apartado "Invocación de funciones" de la sección Notas 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.

5 de diciembre de 2005
Contenido modificado:
  • Se ha aclarado la definición de la opción ENCRYPTION.