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 an Adaptive Server Anywhere stored procedure skeleton using Database Development tooling, you must have established an ASA connection profile.
In Database Explorer under an Adaptive Server Anywhere connection profile, right-click the Procedures & Functions folder.
Select New|Procedure to invoke the Stored Procedure wizard.
The Create Stored Procedure Skeleton page appears.
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.
Choose the SQL dialect you are using: Transact-SQL or Watcom-SQL.
Click Next to add parameters or click Finish if you have no parameters or variables to declare.
The Provide Optional Parameters Declaration page appears.
Select the parameter used by your stored procedure:
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. |
Whether or not a SQLCODE or SQLSTATE parameter is specified as output, their special values can be checked immediately after a procedure call to test the return status of the procedure.
If you selected the User-Defined radio button, enter a parameter name in the Name column. Otherwise, go to step 9.
Click the Datatype column to select a datatype from the drop-down list.
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.
(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.
In the Name column, enter any local variable names that you want to declare.
Click the Datatype column to select a datatype from the drop-down list.
(Optional) Specify the default value for the parameter you are defining.
(Watcom-SQL only) Click Next.
If you are developing Watcom-SQL, the Provide Optional Return Results Information page is displayed.
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.
Click Finish.
A stored procedure skeleton is opened in SQL Editor. You can add SQL statements in SQL Editor.
Send your feedback on this help topic to Sybase Tech Pubs: pubs@sybase.com