Compartir a través de

  • Artículo

ALTER FUNCTION (Transact-SQL)

Modifica una función Transact-SQL o CLR existente, creada anteriormente por medio de la ejecución de la instrucción CREATE FUNCTION, sin cambiar los permisos y sin que afecte a ninguna otra función, procedimiento almacenado o desencadenador dependiente.

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

Sintaxis

Scalar Functions
ALTER 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
ALTER 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
ALTER 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
ALTER 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>::=
    assembly_name.class_name.method_nameFunction 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
    Nombre del esquema al que pertenece la función definida por el usuario.

  • function_name
    Es la función definida por el usuario que se va a cambiar.

    [!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 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 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 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 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 de parámetro en funciones Transact-SQL o CLR.

    Si no se especifica type_schema_name, SQL Server 2005 Database Engine (Motor de base de datos de SQL Server 2005) busca 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 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 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 devuelta de TABLE.

  • scalar_expression
    Especifica que la función escalar devuelve un valor 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 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.

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

  • EXTERNAL NAME <especificador_de_método>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 de 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 la opción, utilice sp_configure.

  • <definición_de_tipo_de_tabla> ( { <definición_de_columna> <restricción_de_columna>   | <definición_de_columna_calculada> }   [ <restricción_de_tabla> ] [ ,...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.

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

<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) cifra las columnas de vista de catálogo que contienen el texto de la instrucción ALTER FUNCTION. El uso de ENCRYPTION impide que la función se publique como parte de la replicación de SQL Server. ENCRYPTION 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 solamente 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.

    Para obtener una lista de las condiciones que se deben cumplir para poder enlazar un esquema a la función, vea CREATE FUNCTION (Transact-SQL).

  • 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 pase 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 especificado en <especificador_de_método> ya tiene un atributo personalizado que indica RETURNS NULL ON NULL INPUT, pero la instrucción ALTER FUNCTION indica CALLED ON NULL INPUT, la instrucción ALTER 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 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 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 proporcionado para la columna cuando no se proporciona 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 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).

    Solamente se puede utilizar la cláusula COLLATE para cambiar las intercalaciones de las columnas cuyo tipo de datos sea char, varchar, nchar y nvarchar.

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

  • ROWGUIDCOL
    Indica que la nueva columna es una columna de identificador único global de fila. Solamente 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). 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 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 Database Engine (Motor de base de datos) el nivel hoja de cada página de índice cuando se crea o se modifica un índice. 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 admiten los bloqueos de página. El valor predeterminado es ON.

Notas

No es posible utilizar ALTER FUNCTION para cambiar una función escalar por una función con valores de tabla, ni viceversa. Tampoco es posible utilizar ALTER FUNCTION para cambiar una función insertada por una función de múltiples instrucciones o viceversa. No se puede utilizar ALTER FUNCTION para cambiar una función Transact-SQL por una función CLR o viceversa.

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

Permisos

Requiere el permiso ALTER para la función o para el esquema. Si la función especifica un tipo definido por el usuario, requiere el permiso EXECUTE para ese tipo.