Creating a SQL Anywhere or Sybase IQ Stored Procedure

A stored procedure is a group of SQL statements and optional control-of-flow (if, else, while statements) that form a logical unit and perform a particular task. Stored procedures are used to encapsulate a set of operations or queries to execute on a database server. To create a SQL Anywhere or Sybase IQ stored procedure skeleton using Database Development tooling, you must have established a connection profile to one of these data servers.

1. Right-click the Stored Procedures folder, and choose New > Stored Procedure from the context menu.

   The Create Stored Procedure Skeleton page appears.

2. Enter the stored procedure name in the Name field, and an optional description of your stored procedure in the Comments field.

   The Preview field displays the syntax generated so far.

3. Choose the SQL dialect you are using: Transact-SQL or Watcom-SQL.
4. Click Next to add parameters.

   The Provide Optional Parameters Declaration page appears.

5. Select the parameter used by your stored procedure:

   Table 1. Parameter types

   Type	Action
   User-Defined	You define the output parameter.
   SQLCODE	Outputs the SQLCODE value when the procedure ends.
   SQLSTATE	Outputs the SQLSTATE value when the procedure ends.

   Note: If a SQLCODE or SQLSTATE parameter is specified as output, its special value can be checked immediately after a procedure call to test the return status of the procedure.
6. If you selected the User-Defined radio button, enter a parameter name in the Name column. Otherwise, go to step 11.
7. Click the Datatype column to select a datatype from the drop-down list.
8. Click the IN/OUT column to select the available input or output option.

   Click Finish if you have no variables to declare. Otherwise, click Next.

9. (Optional) Specify the default value for the parameter you are defining.

   The Provide Optional Variables Declaration page appears. Alternatively, click Finish if you have no variables to declare.

10. In the Name column, enter any local variable names that you want to declare.
11. Click the Datatype column to select a datatype from the drop-down list.
12. (Optional) Specify the default value for the parameter you are defining.
13. (Watcom-SQL only) Click Next.

    If you are developing Watcom-SQL, the Provide Optional Return Results Information page is displayed.

14. In the Results table, specify any number of columns that are part of the result set by entering each result variable name in the Name column (for example, myCalculation1) and selecting its datatype from the Datatype list.
15. Click Next, and specify save and execution properties for the stored procedure.

    Field	Description
    Generate script	Select this option to generate the script for the stored procedure.
    Enter or select the parent folder	Specify an existing project or create a new project in which to save the SQL.
    File name	Enter the name of the file in which to store the SQL.
    Execute immediately	Execute the SQL immediately. If you do not select this option, you must complete execution of the SQL later to create the stored procedure.
    Open in editor after creation	Create the stored procedure, and then open it in the Stored Procedure Editor.

16. Click Finish to create the stored procedure.

    The newly created stored procedure is opened automatically in the Stored Procedure Editor.

Send your feedback on this help topic to Sybase Technical Publications: pubs@sybase.com

Your comments will be sent to the technical publications staff at Sybase, Inc. For product-related issues or technical support, contact Sybase Technical Support at 1-800-8SYBASE.