ALTER PROCEDURE Statement

Syntax

Syntax 1

ALTER PROCEDURE [ owner.]procedure-name procedure-definition

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

Syntax 5

ALTER PROCEDURE 
[ owner.]procedure-name ( [ parameter, …] )
[  RESULT (result-column, ...)]
EXTERNAL NAME ‘external-call’ [ LANGUAGE environment-name ] }

Parameters

• procedure-definition – CREATE PROCEDURE syntax following the name
• environment-name – JAVA [ DISALLOW | ALLOW SERVER SIDE REQUESTS ]
• external-call – [column-name:]function-name@library; ...

Usage

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

The ALTER PROCEDURE statement is identical in syntax to the CREATE PROCEDURE statement.

• Syntax 1 – 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 permissions on the procedure are not changed. If you execute DROP PROCEDURE followed by CREATE PROCEDURE, execute permissions are reassigned.

• Syntax 2 – If a procedure needs to be relocated to other sites using Sybase Replication Server, 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. It is recommended that you retain the original procedure definition outside of the database.

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

You cannot combine Syntax 2 with Syntax 1.

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

• Syntax 5 – Use this syntax for Java UDFs.

  iq-environment-name: JAVA [ DISALLOW | ALLOW SERVER SIDE REQUESTS ]:

  DISALLOW is the default.

  ALLOW indicates that server-side connections are allowed.

  Note: Do not specify ALLOW unless necessary. A setting of ALLOW slows down certain types of Sybase IQ table joins.

  Do not use UDFs with both ALLOW SERVER SIDE REQUESTS and DISALLOW SERVER SIDE REQUESTS in the same query.

When using the ALTER PROCEDURE statement for table UDFs, the same set of restrictions apply as for the CREATE PROCEDURE Statement (External Procedures).

Standards

Permissions

Must be the owner of the procedure or a DBA. Automatic commit.