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

SAP Sybase SQL Anywhere 16.0 » SQL Anywhere Server - SQL Usage » Stored procedures, triggers, batches, and user-defined functions » User-defined functions


Calling a user-defined function

A user-defined function can be used in any place you would use a built-in non-aggregate function.


You must have EXECUTE privilege on the function.

  1. In Interactive SQL, connect to the database.

  2. Execute a SELECT statement using the user-defined function.


The function is called and executed.


  • Example 1: Call a user-defined function   The following function concatenates a firstname string and a lastname string.
    CREATE FUNCTION fullname(
       firstname CHAR(30),
       lastname CHAR(30) )
       DECLARE name CHAR(61);
       SET name = firstname || ' ' || lastname;
       RETURN (name);

    Execute the following statement in Interactive SQL to return a full name from two columns containing a first and last name:

    SELECT FullName( GivenName, Surname ) 
     AS "Full Name"
     FROM Employees;
    Full Name
    Fran Whitney
    Matthew Cobb
    Philip Chin

    Execute the following statement in Interactive SQL to use the FullName user-defined function to return a full name from a supplied first and last name:

    SELECT FullName('Jane', 'Smith') 
     AS "Full Name";
    Full Name
    Jane Smith

  • Example 2: Local declarations of variables   The following user-defined function illustrates local declarations of variables.


    While this function is useful for illustration, it may perform poorly if used in a SELECT involving many rows. For example, if you used the function in the SELECT list of a query on a table containing 100000 rows, of which 10000 are returned, the function is called 10000 times. If you use it in the WHERE clause of the same query, it would be called 100000 times.

    The Customers table includes Canadian and American customers. The user-defined function Nationality forms a three-letter country code based on the Country column.

    CREATE FUNCTION Nationality( CustomerID INT )
        DECLARE nation_string CHAR(3);
        DECLARE nation country_t;
        SELECT DISTINCT Country INTO nation 
        FROM Customers 
        WHERE ID = CustomerID;
        IF nation = 'Canada' THEN
                SET nation_string = 'CDN';
        ELSE IF nation = 'USA' OR nation = ' ' THEN
                SET nation_string = 'USA';
                SET nation_string = 'OTH';
            END IF;
        END IF;
    RETURN ( nation_string );

    This example declares a variable named nation_string to hold the nationality string, uses a SET statement to set a value for the variable, and returns the value of nation_string to the calling environment.

    The following query lists all Canadian customers in the Customers table:

    SELECT *
    FROM Customers
    WHERE Nationality( ID ) = 'CDN';

 See also