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

SQL Anywhere 17 » SQL Anywhere Server - SQL Reference » SQL statements » Alphabetical list of SQL statements


Modifies a procedure.

  • Change the definition of a procedure
    ALTER PROCEDURE [ owner.]procedure-name procedure-definition
    procedure-definition : see the CREATE PROCEDURE statement
  • Obfuscate a procedure definition
    ALTER PROCEDURE [ owner.]procedure-name 
  • Recompile a procedure
    ALTER PROCEDURE [ owner.]procedure-name 

The ALTER PROCEDURE statement must include the entire new procedure. You can use PROC as a synonym for PROCEDURE.

  • Change the definition of a procedure

    The ALTER PROCEDURE statement is identical in syntax to the CREATE PROCEDURE statement except for the first word. Both Watcom and Transact-SQL dialect procedures can be altered through the use of ALTER PROCEDURE.

    With ALTER PROCEDURE, existing privileges on the procedure are not changed. If you execute DROP PROCEDURE followed by CREATE PROCEDURE, execute privileges are reassigned.

  • Obfuscate a procedure definition

    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.

    If SET HIDDEN is used, debugging using the debugger does not show the procedure definition, and the definition is not available through procedure profiling.

    Note This change is irreversible. It is recommended that you retain the original procedure definition outside of the database.
  • Recompile a procedure

    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.

Note For required parameters that accept variable names, an error is returned if one of the following conditions is true:
  • The variable does not exist
  • The contents of the variable are NULL
  • The variable exceeds the length allowed by the parameter
  • The data type of the variable does not match that required by the parameter

You must be the owner of the procedure or have one of the following privileges:

  • ALTER ANY PROCEDURE system privilege
  • ALTER ANY OBJECT system privilege
Side effects

Automatic commit.

  • ANSI/ISO SQL Standard

    ALTER PROCEDURE is optional ANSI/ISO SQL Language Feature F381. However, in the SQL standard, ALTER PROCEDURE cannot be used to re-define a stored procedure definition, and Transact-SQL dialect procedures are not supported. The ANSI/ISO SQL Standard does not include support for SET HIDDEN or RECOMPILE.


  1. The following example creates and then alters a procedure using a variable in the NAMESPACE clause

    1. The following statements create a variable for a NAMESPACE clause:

      SET @ns = '';
    2. The following statement creates a procedure named FtoC that uses a variable in the NAMESPACE clause:

      URL 'http://localhost:8082/FtoCService'
      NAMESPACE @ns;
    3. The following statement alters the procedure FtoC so that the temperature parameter accepts a FLOAT data type:

      ALTER PROCEDURE FtoC ( IN temperature FLOAT )
      URL 'http://localhost:8082/FtoCService'
      NAMESPACE @ns;