Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 11.0.1 » SQL Anywhere Server - SQL Reference » Using SQL » SQL functions » Function types


User-defined functions

A user-defined function, or UDF, is a function created by the user of a program or environment. User-defined functions are in contrast to functions that are built in to the program or environment.

There are two mechanisms for creating user-defined functions in SQL Anywhere. You can use the SQL language to write the function, or you can use Java.

User-defined functions in SQL

You can implement your own functions in SQL using the CREATE FUNCTION statement (web services). The RETURN statement inside the CREATE FUNCTION statement determines the data type of the function.

Once a SQL user-defined function is created, it can be used anywhere a built-in function of the same data type is used.

For more information about creating SQL functions, see Using procedures, triggers, and batches.

User-defined functions in Java

Java classes provide a more powerful and flexible way of implementing user-defined functions, with the additional advantage that they can be moved from the database server to a client application if desired.

Any class method of an installed Java class can be used as a user-defined function anywhere a built-in function of the same data type is used.

Instance methods are tied to particular instances of a class, and so have different behavior from standard user-defined functions.

For more information about creating Java classes, and on class methods, see Creating a class.

Deciding whether to create a user-defined functions or a procedure

Functions are similar to procedures. Deciding whether to create a function or a procedure depends on what you want returned, and the object will be called. When deciding whether to create a UDF or a procedure, consider their unique characteristics listed below.


  • can return a single value of arbitrary type, and allow you to declare the returned type using the RETURNS clause

  • can be used in most places an expression can be used

  • allow you to define only IN parameters


  • can return multiple values using INOUT or OUT parameters

  • can return result sets

  • can be referenced in the FROM clause of a query, or using a CALL statement, or using a Transact-SQL EXECUTE statement

  • can be called using named parameters