create function

Syntax

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]

Parameters

• or replace – redefines 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.
• owner_name – is the name of the user ID that owns the user-defined function. Must be an existing user ID.
• function_name – is the name of the user-defined function. Function names must conform to the rules for identifiers and must be unique within the database and to its owner. Function names cannot be the same as other SAP ASE functions.

  When used with or replace, is the name of the function remains the same, although its definition is changed.

  Note: To reference or invoke a user-defined function, specify the owner_name.function_name, followed by parentheses (see the BONUS function in the “Examples” section below). Specify expressions as arguments for all the parameters within the parentheses. You cannot specify the parameter names in the argument list when you invoke a function.You must supply argument values for all of the parameters, and the argument values must be in the same sequence in which the parameters are defined in the create function statement. When a function’s parameter has a default value, you must specify the keyword “default” when calling the function to get the default value.
• @parameter_name – is the parameter in the user-defined function. You can declare one or more parameters in a create function statement. A function can have a maximum of 2,047 parameters. The value of each declared parameter must be supplied by the user when the function is executed, unless you define a default for the parameter.

  Specify a parameter name using an “at” sign (@) as the first character. The parameter name must conform to the rules for identifiers. Parameters are local to the function. You can use the same parameter names in other functions.

  If a parameter has a default value, the user must specify the keyword “default” when they call the function to get the default value.

  When used with or replace, you can alter the names and number of parameters.

• parameter_datatype – is the datatype of the parameter. All scalar datatypes and Java abstract datatypes (ADTs) can be used as a parameter for user-defined functions. However, user-defined functions do not support the timestamp, text, image and unitext.

  When used with or replace, you can change the datatype of the parameter to the function.

• with recompile – indicates that the SAP ASE server never saves a plan for this function; instead, a new plan is created each time the function is referenced in a SQL statement. Use with recompile when you expect the execution of this function to be atypical, and require a new plan.

  When used with or replace, you can change the option to recompile, or not to recompile every time the function is replaced.

• return_datatype – is the return value of a scalar, user-defined function. return_datatype can be any of the scalar datatypes and Java abstract datatypes except text, image, unitext, and timestamp.

  When used with or replace, you can alter the return datatype of the function.

• scalar_expression – specifies the scalar value the scalar function returns.

  You can invoke scalar-valued functions where scalar expressions are used, including computed columns and check constraint definitions.

  When used with or replace, you can change the value returned by the function.

• function_body – specifies a series of Transact-SQL statements, which together do not produce a side effect but define the value of the function. function_body is used only in scalar functions and multistatement table-valued functions. In scalar functions, function_body is a series of Transact-SQL statements that evaluate to a scalar value.

  When used with or replace, you can change the SQL statements that define the value of the function

Examples

• Example 1 – Creates a user-defined function named bonus:

  create function BONUS(@salary int, @grade int, @dept_id int)
  returns int
  as
      begin
      declare @bonus int
      declare @cat int
      set @bonus = 0
      select @cat = dept_cat from department 
          where dept_id = @dept_id
  
      if (@cat < 10)
          begin
              set @bonus = @salary *15/100
  
          end
          else
              begin
              set @bonus = @salary * 10/100
          end
  return @bonus
  end

• Example 2 – 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   

Usage

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

Permissions

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

The permission checks for create function differ based on your granular permissions settings.

Auditing

Values in event and extrainfo columns of sysaudits are: