Creating user-defined functions

You use the CREATE FUNCTION statement to create user-defined functions. You must have RESOURCE authority to execute this statement.

The following simple example creates a function that concatenates two strings, together with a space, to form a full name from a first name and a last name.

CREATE FUNCTION FullName( FirstName CHAR(30),
   LastName CHAR(30) )
RETURNS CHAR(61)
BEGIN
   DECLARE name CHAR(61);
   SET name = FirstName || ' ' || LastName;
   RETURN ( name );
END;

The CREATE FUNCTION syntax differs slightly from that of the CREATE PROCEDURE statement. The following are distinctive differences:

  • No IN, OUT, or INOUT keywords are required, as all parameters are IN parameters.
  • The RETURNS clause is required to specify the data type being returned.
  • The RETURN statement is required to specify the value being returned.

You can also create user-defined functions from Sybase Central.

To create a user-defined function (Sybase Central)

  1. Connect to the database as a user with DBA or Resource authority.

  2. In the left pane, click Procedures & Functions.

  3. Choose File » New » Function.

  4. Follow the instructions in the Create Function Wizard.

  5. In the right pane, click the SQL tab to complete the procedure code.

    The new function appears in Procedures & Functions.

See also