create function


Creates a user-defined function, which is a saved Transact-SQL routine that returns a specified value.


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



is the name of the user ID that owns the user-defined function. Must be an existing user ID.


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 Adaptive Server functions.

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


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.


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.

with recompile

indicates that Adaptive 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.


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


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.


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.


Example 1

Creates a user-defined function named bonus:

create function BONUS(@salary int, @grade int, @dept_id int)
returns int
    declare @bonus int
    declare @cat int
    set @bonus = 0
    select @cat = dept_cat from department 
        where dept_id = @dept_id

    if (@cat < 10)
            set @bonus = @salary *15/100

            set @bonus = @salary * 10/100
return @bonus



create function permission defaults to the database owner, who can transfer it to other users.

Owners of functions have execute permission on their functions. Other users do not have execute permissions unless execute permissions on the specific function are granted to them.