create function

The or replace clause allows you to replace a user-defined function's definition using 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

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.

Granular permissions enabled With granular permissions enabled, you must have the create function privilege. You must have the create any function privilege to run create function for other users. You must be the function owner to replace the function.
Granular permissions disabled With granular permissions disabled, you must be the database owner or have the create function privilege. You must be the function owner to replace the function.

Auditing Changes for create or replace function

Changes are in bold.

Event Audit Option Command or access audited Information in extrainfo
97 create create function
  • Roles – current active roles
  • Keywords or options – NULL
  • Previous value – NULL
  • Other information – NULL
  • Current value – NULL
  • Proxy information – original login name, if set proxy is in effect
  • or replace – for create or replace