create function

Description

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

Syntax

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]

Parameters

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

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.

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

parameter_datatype: is 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.

return_datatype: is 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.

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

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.

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

Usage

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.
When a function is created, Adaptive Server checks to see if it is a SQL user-defined function or a SQLJ user-defined function. If it is the latter, Adaptive Server checks for “sa” permissions. If it is a SQL function Adaptive Server checks for create function privileges.

Permissions

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.