User's Guide

Part III. Using SQL Anywhere

Chapter 20. Using Procedures, Triggers, and Batches

This section provides some pointers for developing procedures.

Check if you need to change the command delimiter

You do not need to change the command delimiter in ISQL or Sybase Central when you are writing procedures. However, if you are creating and testing procedures and triggers from some other browsing tool, you may need to change the command delimiter from the semicolon to another character. Each statement within the procedure ends with a semicolon. For some browsing applications to parse the CREATE PROCEDURE statement itself, you need the command delimiter to be something other than a semicolon. If you are using an application that requires changing the command delimiter, a good choice is to use two semicolons as the command delimiter (;;) or a question mark (? ) if the system does not permit a multicharacter delimiter.

Remember to delimit statements within your procedure

You should terminate each statement within the procedure with a semicolon. Although you can leave off semicolons for the last statement in a statement list, it is good practice to use semicolons after each statement.

The CREATE PROCEDURE statement itself contains both the RESULT specification and the compound statement that forms its body. No semicolon is needed after the BEGIN or END keywords, or after the RESULT clause.

Use fully-qualified names for tables in procedures

If a procedure has references to tables in it, you should always preface the table name with the name of the owner (creator) of the table.

When a procedure refers to a table, it uses the group memberships of the procedure creator to locate tables with no explicit owner name specified. For example, if a procedure created by user_1 references Table_B and does not specify the owner of Table_B, then either Table_B must have been created by user_1 or user_1 must be a member of a group (directly or indirectly) that is the owner of Table_B. If neither condition is met, a

table not found

You can minimize the inconvenience of long fully qualified names by using a correlation name to provide a convenient name to use for the table within a statement. Correlation names are described in "FROM clause".

Specifying dates and times in procedures

When dates and times are sent to the database from procedures, they are sent as strings. The string is interpreted according to the current setting of the DATE_ORDER database option. As different connections may set this option to different values, some strings may be converted incorrectly to dates, or the database may not be able to convert the string to a date.

You should use the unambiguous date format yyyy-mm-dd oryyyy/mm/dd when sending dates to the database from procedures. These strings are interpreted unambiguously as dates by the database, regardless of the DATE_ORDER database option setting.

  For more information on dates and times, see "Date and time data types".

Verifying that procedure input arguments are passed correctly

You can verify that input arguments to a procedure are passed correctly in several ways. For example:

• Display the value of the parameter on the message window of the database engine or server using the MESSAGE statement. For example, the following procedure simply displays the value of the input parameter var :

       CREATE PROCEDURE message_test (IN var char(40))
       BEGIN
           MESSAGE var;
       END
  

• From ISQL:
• Create a variable to hold the argument (say GBLVAR).
• Assign the argument to GBLVAR inside the procedure:

       SET GBLVAR = argument
  

• After calling the procedure, display the value of GBLVAR:

       SELECT GBLVAR
  

• Assign the input value to an output parameter, and test the value of the output parameter on completion.
• Insert the argument into a table, and SELECT from the table.