ALTER PROCEDURE
Statement
Replaces an existing procedure with a modified version. Include the entire modified procedure in the ALTER PROCEDURE statement, and reassign user permissions on the procedure.
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 JAVA [ environment-name ] } external-call - (back to Syntax 5) [<column-name>:]<function-name@library>; ... environment-name - (back to Syntax 5) DISALLOW | ALLOW SERVER SIDE REQUESTS
- CREATE PROCEDURE syntax following the name.
- if a procedure needs to be relocated to other sites using SAP Replication Server, use the REPLICATE ON clause.
- 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.
- recompiles a stored procedure. When you recompile a procedure,
the definition stored in the catalog is re-parsed and the syntax is
verified.
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.
- 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.
- DISALLOW is the default. ALLOW indicates that server-side
connections are allowed.Note
- Do not specify ALLOW unless necessary. Use of teh ALLOW clause slows down certain types of SAP IQ table joins.
- Do not use UDFs with both ALLOW SERVER SIDE REQUESTS and DISALLOW SERVER SIDE REQUESTS clauses in the same query.
The ALTER PROCEDURE statement must include the entire new procedure. You can use PROC as a synonym for PROCEDURE. Both Watcom and Transact-SQL® dialect procedures can be altered through the use of ALTER PROCEDURE. Existing permissions on the procedure are not changed. If you execute DROP PROCEDURE followed by CREATE PROCEDURE, execute permissions are reassigned.
You cannot combine Syntax 2 with Syntax 1.
When using the ALTER PROCEDURE statement for table UDFs, the same set of restrictions apply as for the CREATE PROCEDURE Statement (External Procedures).
- SQL—Vendor extension to ISO/ANSI SQL grammar.
- SAP Database products—Not supported by SAP® Adaptive Server® Enterprise (SAP ASE).