ALTER FUNCTION

Alter a user-defined function.

Syntax
-- Transact-SQL Scalar Function Syntax
      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
      [ ; ]

-- Transact-SQL Inline Table-Valued Function Syntax
      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 [ ) ]
[ ; ]


-- Transact-SQL Multistatement Table-valued Function Syntax
      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
      [ ; ]

-- 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 and Table-Valued Function Syntax
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
[ ; ]

-- CLR Function Clauses
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 ] )

-- Syntax for In-Memory OLTP: Natively compiled, scalar user-defined function  
ALTER FUNCTION [ schema_name. ] function_name    
 ( [ { @parameter_name [ AS ][ type_schema_name. ] parameter_data_type   
    [ NULL | NOT NULL ] [ = default ] }   
    [ ,...n ]   
  ]   
)   
RETURNS return_data_type  
    [ WITH function_option [ ,...n ] ]   
    [ AS ]   
    BEGIN ATOMIC WITH (set_option [ ,... n ])  
        function_body   
        RETURN scalar_expression  
    END  

function_option::=   
{ |  NATIVE_COMPILATION   
  |  SCHEMABINDING   
  | [ EXECUTE_AS_Clause ]   
  | [ RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT ]   
}

Key:
    function_name       Name of the function(s) to add.

    schema_name       The schema to which the user-defined function belongs.

    parameter_data_type The data type of the function (not timestamp) 
                        CLR functions, accept a limited range of data types.

    @parameter_name     A parameter in the user-defined function. One or more parameters can be declared.

   [type_schema_name.] parameter_data_type
                        Is the parameter data type and optionally, the schema to which it belongs.
                        For Transact-SQL functions, all data types, including CLR user-defined
                        types, are allowed except the timestamp data type.
                        For CLR functions, all data types, including CLR user-defined types, are allowed
                        except text, ntext, image, and timestamp data types.
                        The nonscalar types cursor and table cannot be specified as a parameter data type
                        in either Transact-SQL or CLR functions.

“Form follows function” ~ Louis H. Sullivan

Related commands

CREATE FUNCTION
DROP FUNCTION