ALTER PROCEDURE

<< CREATE PROCEDURE | FB 2.5 Language Reference | CREATE OR ALTER PROCEDURE >>

ALTER PROCEDURE

Classic Server: Altered procedure immediately
visible to other clients
Default argument values
COLLATE in variable and parameter
declarations
Domains supported in parameter and
variable declarations
NOT NULL in variable and parameter
declarations
Restriction on altering used procedures

`ALTER PROCEDURE`

Available in: DSQL, ESQL

Default argument values

Added in: 2.0

Description

You can now provide default values for stored procedure arguments, allowing the caller to omit one or more items from the end of the argument list. See CREATE PROCEDURE for syntax and details.

Example

 alter procedure TestProc
 (a int, b int default 1007, s varchar(12) = '-')
 ...

Classic Server: Altered procedure immediately visible to other clients

Changed in: 2.5

Description

Traditionally, when a client used ALTER PROCEDURE on a Classic server, other clients would keep seeing (and possibly executing) the old version for the duration of their connection. This has been fixed in 2.5. Now, all clients see the new version as soon as the changes have been committed.

`COLLATE` in variable and parameter declarations

Changed in: 2.1

Description

Firebird 2.1 and up allow COLLATE clauses in declarations of input/output parameters and local variables. See CREATE PROCEDURE for syntax and details.

Domains supported in parameter and variable declarations

Changed in: 2.1

Description

Firebird 2.1 and up allow the use of domains instead of SQL datatypes when declaring input/output parameters and local variables. See CREATE PROCEDURE for syntax and details.

`NOT NULL` in variable and parameter declarations

Changed in: 2.1

Description

Firebird 2.1 and up allow NOT NULL constraints in declarations of input/output parameters and local variables. See CREATE PROCEDURE for syntax and details.

Restriction on altering used procedures

Changed in: 2.0, 2.0.1

Description

In Firebird 2.0 only, a restriction is in place which prevents anyone from dropping, altering or recreating a trigger or stored procedure if it has been used since the database was opened. This restriction has been removed again in version 2.0.1. Still, performing these operations on a live database is potentially risky and should only be done with the utmost care.