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] ]
as
[begin]
function_body
return scalar_expression
[end]
owner_name – the name of the user ID that owns the user-defined function. Must be an existing user ID.
function_name – 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.
@parameter_name – 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.
Parameter_datatype – the data type of the parameter. All scalar data types 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 first referenced in a SQL statement. Use with recompile when you expect the execution of this function is atypical, and it will require a new plan.
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 for an example). 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.
You can invoke scalar-valued functions where scalar expressions are used, including computed columns and check constraint definitions.
return_datatype – the return value of a scalar, user-defined function. It can be any of the scalar data types and Java ADTs except text, image, unitext and timestamp.
scalar_expression – specifies the scalar value the scalar function returns.
function_body – specifies a series of T-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 multi-statement table-valued functions. In scalar functions, function_body is a series of T-SQL statements that evaluate to a scalar value.
This example 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
If the owner of the user-defined function also owns all the database objects referenced inside, then all the other users who have execute permission on the function are automatically granted access permissions to all the referenced objects when they execute the function.
By default, users with the sa and the dbo role have permission to run create function. Users with the sa or dbo role can grant create function permissions to other logins.
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.
