Editing a Stored Procedure

Edit a stored procedure to change the SQL code or permissions.

Prerequisites

Before you can edit a stored procedure, be sure the data server that contains the stored procedure is running, and you created and established a connection to the data server.

In the Databases folder in Enterprise Explorer, under the database connection profile, expand the navigation tree for the appropriate database and schema owner.

Database	Steps
From an ASE connection profile	Expand the Databases folder. Expand the tree for a database and then the tree for the appropriate schema owner.
From a SQL Anywhere or Sybase IQ connection profile	Expand the tree for a database. Expand the Schemas folder and then the tree for the appropriate schema owner.

Expand the Stored Procedures folder, right-click the stored procedure you want to edit, and choose Edit.

Edit the stored procedure properties.

Editor Tab	Description
General	Change the stored procedure name in the Name field. Select the parameters to be used in the stored procedure. To delete a particular parameter, highlight the entry and click the Delete button. To delete all parameters, click the Delete All button. Use the Up and Down buttons to change the order of parameters listed. Specify the transaction mode for the stored procedure. In CHAINED mode, the stored procedure implicitly begins a transaction before any data retrieval or modification statement. In UNCHAINED mode, the stored procedure requires an explicit begin transaction statement paired with a commit transaction or rollback transaction statement to complete a transaction. The ANYMODE mode lets a stored procedure run under either the chained or unchained transaction mode. Check the With Recompile check box to have the stored procedure recompile before every execution.
Source	Manually edit the SQL code for the stored procedure in the Source tab. To display line numbers for the SQL code, right-click in the left margin, and choose Show Line Numbers from the context menu.
Permissions	Use this tab to set permissions for the stored procedure. You can specify Granted, Granted with Grant Option, Inherited, and Revoked Inherited Permission for the listed roles.
DDL	Select this tab to view the DDL for the stored procedure. Content in this tab is not modifiable.

When you are finished editing the stored procedure, choose File > Save from the main WorkSpace menu.
Note: When you save a SQL Anywhere or Sybase IQ stored procedure in the editor, the SQL is first transformed into the canonical form so that the database engine can execute the procedure. The editor then displays the canonical form of the stored procedure in place of the original SQL text. Consequently, saving a stored procedure in the editor might change line numbers, wrapping and syntax from the original. However, the SQL code displayed after a save executes exactly as the originally entered SQL code because the code displayed after the save is what the database engine would execute.