Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 11.0.1 (Français) » SQL Anywhere Server - SQL Reference » Using SQL » SQL statements » SQL statements (A-D)

 

ALTER PROCEDURE statement

Use this statement to modify a procedure, or to enable and disable a procedure for replication with Sybase Replication Server. You must include the entire new procedure in the ALTER PROCEDURE statement.

You can use PROC as a synonym for PROCEDURE.

Syntax 1
ALTER PROCEDURE [ owner.]procedure-name procedure-definition
procedure-definition : CREATE PROCEDURE syntax
Syntax 2
ALTER PROCEDURE [ owner.]procedure-name
REPLICATE { ON | OFF }
Syntax 3
ALTER PROCEDURE [ owner.]procedure-name 
SET HIDDEN
Syntax 4
ALTER PROCEDURE [ owner.]procedure-name 
RECOMPILE
Remarks

Syntax 1   The ALTER PROCEDURE statement is identical in syntax to the CREATE PROCEDURE statement except for the first word. Either version of the CREATE PROCEDURE statement can be altered.

Existing permissions on the procedure are maintained, and do not have to be reassigned. If a DROP PROCEDURE and CREATE PROCEDURE were carried out, execute permissions would have to be reassigned.

Syntax 2   If a procedure is to be replicated to other sites using Sybase Replication Server, you must set REPLICATE ON for the procedure.

Syntax 3   Use SET HIDDEN to obfuscate the definition of the associated procedure and cause it to become unreadable. The procedure can be unloaded and reloaded into other databases.

Note

This setting is irreversible. If you will need the original source again, you must maintain it outside the database.

If SET HIDDEN is used, debugging using the debugger will not show the procedure definition, nor will it be available through procedure profiling.

You cannot combine Syntax 2 with Syntax 1. You cannot combine Syntax 3 with either Syntax 1 or 2.

Syntax 4   Use the RECOMPILE syntax to recompile a stored procedure. When you recompile a procedure, the definition stored in the catalog is re-parsed and the syntax is verified. For procedures that generate a result set but do not include a RESULT clause, the database server attempts to determine the result set characteristics for the procedure and stores the information in the catalog. This can be useful if a table referenced by the procedure has been altered to add, remove, or rename columns since the procedure was created.

The procedure definition is not changed by recompiling. You can recompile procedures with definitions hidden with the SET HIDDEN clause, but their definitions remain hidden.

Permissions

Must be the owner of the procedure or have DBA authority.

Side effects

Automatic commit.

See also
Standards and compatibility
  • SQL/2003   Vendor extension.