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 encapsulate a set of operations or queries to execute on a database server.

1. In the Databases folder in Enterprise Explorer, under the database connection profile, expand the tree for a database, expand the Schemas folder and then the tree for the appropriate schema owner.
2. Right-click the Stored Procedures folder, and choose New > Stored Procedure .
3. Enter the Name for the stored procedure.
4. (Optional) Enter a description in the Comments field.
5. Select the SQL Dialect: Transact-SQL or Watcom-SQL.
6. Click Next to add parameters.
7. 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 you specify a SQLCODE or SQLSTATE parameter as output, its special value can be checked immediately after a procedure call to test the return status of the procedure.
8. If you selected User-Defined, enter a parameter name in the Name column.

   Otherwise, go to step 11.

9. Select a Datatype.
10. Select the IN/OUT option.
11. Select Finish if you have no variables to declare; otherwise, click Next.
12. (Optional) Specify the default value for the parameter you are defining, or select Finish if you have no variables to declare.
    1. Enter the Name for any local variables that you want to declare.
    2. Select the Datatype.
    3. (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.
15. Click Next and specify save and execution properties.

    Field	Description
    Generate Script	Generate a SQL file for the object.
    Enter or Select the Parent Folder	Specify an existing project or create a new project in which to save the SQL file.
    File Name	Enter the file name in which to store the SQL code.
    Execute Immediately	Execute the SQL code immediately. If you do not select this option, you must complete execution of the SQL file later to create the object.
    Open in Editor After Creation	Create the object and open it in its associated editor.

16. Click Finish to create the stored procedure.