create function

Syntax

Changes are in bold.

create [or replace] function           
    [ owner_name.] function_name            
    [ ( @parameter_name [as] parameter_datatype            
    [ = default ][ ,...n ] ) ]
    returns  return_datatype           
    [ with recompile]           
    as            
    [begin]           
    function_body            
    return scalar_expression           
    [end]   

Parameter Changes for create or replace function

• create – creates a function if one does not already exist. 
• or replace – re-defines an existing function. Use this clause to change the definition of an existing user defined function without dropping, re-creating, and regranting object privileges previously granted on the function. If the function is redefined, it is recompiled when the function is used.
• function_name – the name of the function remains the same, although its definition is changed.
• @parameter_name – names and number of parameters can be altered when the function definition is replaced.
• parameter_datatype – you can change the datatype of the parameter to the function.
• with recompile – you can change the option to recompile, or not to recompile every time the function is replaced.
• return_datatype – you can alter the return datatype of the function.
• scalar_expression – you can change the value returned by the function.
• function_body – you can change the SQL statements that define the value of the function.

Example

This example defines a function which concatenates firstname and lastname strings.

create function fullname(           
    @firstname char(30),           
    @lastname char(30))   
returns char(61)   
as   
begin   
declare @name char(61)   
set @name = @firstname|| ‘ ‘ ||@lastname   
return @name   
end       

select object_id("fullname")       
-----------
473049690   

This function replaces the previously created fullname function using the or replace clause. After replacing the function, the local variable @name is removed. The object ID of the function remains the same.

create or replace function fullname(           
    @firstname char(30),           
    @lastname char(30))   
returns char(61)   
as   
begin    
return(@firstname|| ' ' ||@lastname)    
end       

select object_id("fullname")       
-----------
473049690   

Objects Dependent on Replaced Functions

If the replaced function is called by another function, both functions will be recompiled when called. If the interface of the replaced function does not match that in the calling function, then the calling function must be replaced, otherwise the calling function raises an error. You can execute sp_depends on the replaced function to check for any calling objects.

For example, testfun1 is replaced to have two parameters instead of one. The calling function, testfun2, must be replaced to account for the second parameter.

create function testfun1 (@para1 int) 
    returns int 
    as 
    begin 
        declare @retval int 
        set @retval = @para1 
        return @retval 
    end 
create function testfun2 (@para int) 
    returns int 
    as 
    begin 
        declare @retval int 
        select @retval= dbo.testfun1 (@para) 
        return @retval 
    end 
create or replace function testfun1 (@para1 int,@para2 int) 
    returns int 
    as 
    begin 
        declare @retval int 
        set @retval = @para1+@para2 
        return @retval 
    end

Restrictions

If a function is referenced in a computed column or functional index, it cannot be replaced.

Permission Changes for create or replace function

Any user who impersonates the function owner through an alias or setuser cannot replace the function.

Changes for replacing a function are in bold.

Auditing Changes for create or replace function

Changes are in bold.