Editing a Stored Procedure

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

1. 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.

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

   Editor tab	Description
   General	Change the Name. Select the parameters to be used in the stored procedure. To delete a particular parameter, highlight the entry and click Delete. To delete all parameters, click Delete All. Use Up and Down 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. Select With Recompile to recompile the stored procedure before every execution.
   Source	Manually edit the SQL code for the stored procedure in the Source page. To display line numbers for the SQL code, right-click in the left margin, and select Show Line Numbers.
   Debug	Set, enable, or disable the breakpoint and invoke the debugger using the context menu. To set SQL Debugger preferences, right-click in the editor and select Preferences. Note: You cannot change the source code in the Debug editor. You must switch to the Source editor to edit the SQL code.
   Permissions	Set permissions for the stored procedure. You can specify Granted, Granted With Grant Option, Inherited, and Revoked Inherited Permission for the listed roles.
   DDL	View the DDL for the stored procedure (read-only).

4. Select 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.