create procedure (SQLJ)

Previously granted privileges on a replaced procedure are preserved.

Syntax

Changes are in bold.

create [or replace] procedure
             
    [owner_name.]sql_procedure_name
        ([[in | out | inout]  sql_parameter_name 
        sql_datatype     
            [(length)| (precision[, scale ])]
            [=default] 
        ...]) 
        [,[in | out | inout]  sql_parameter_name 
        sql_datatype     
            [(length)| (precision[, scale])]]   
            [=default] 
        ...])     
    [modifies sql data]   
    [dynamic result sets integer]  
    [deterministic | not deterministic]  
    language java   
    parameter style java  
    external name 'java_method_name    
        [([java_datatype[, java_datatype    
        ...]])] '

Parameter Changes for create or replace procedure (SQlJ)

create – if only create is specified, a new SQLJ procedure is created.
or replace – if the specified SQLJ procedure does not exist, a new SQLJ procedure is created. If the SQLJ procedure does exist, the definition is changed.
sql_procedure_name – name and number of parameters can be changed.
in | out | inout – the mode of the listed parameter can be changed.
sql_datatype [(length) (precision[,scale])] – you can change the type, length, precision and scale of the parameters.
default – you can change the default to NULL for the parameters, or set a different value when the procedure is changed.
deterministic | not deterministic – you can change the deterministic value.
external – name of external routine can be changed.
java_method_name – the Java method name can be changed.
java_datatype – the Java datatype can be changed.

Example

This example creates a SQLJ procedure named proc_name.

create procedure sqlj_proc (param int)
                             language java
                             parameter style java
                     external name 'UDFSample.sample(int)'

This procedure replaces the previously created SQLJ procedure using the or replace clause. Parameter p2 is added and the external java method is changed but the object ID of the SQLJ procedure remains the same.

create or replace procedure sqlj_proc (p1 int, p2 int)
                             language java
                             parameter style java
                     external name 'UDFSample.add(int,int)'

Objects Dependent on Replaced Procedures

A Transact-SQL procedure that calls a replaced SQLJ procedure is recompiled when it executes. If replacing the SQLJ procedure changed the number or type of parameters, the calling procedure must be replaced. You can run sp_depends on the replaced procedure to verify whether there are calling procedures that are affected by the changed definition.

Permission Changes for create or replace procedure (SQLJ)

Any user who impersonates the procedure owner through an alias or setuser cannot replace the procedure.

Changes for replacing a SQLJ procedure are in bold.

Granular permissions enabled	When granular permissions is enabled, you must have the `create procedure` privilege. You must have the `create any procedure` privilege to run create procedure for other users. You must be the procedure owner to replace the procedure.
Granular permissions disabled	With granular permissions disabled, you must be the database owner or have the `create procedure` privilege. You must be the procedure owner to replace the procedure.

Auditing Changes for create or replace procedure (SQLJ)

Changes are in bold.

Event	Audit Option	Command or access audited	Information in extrainfo
11	create	create procedure	Roles – current active roles Keywords or options – NULL Previous value – NULL Other information – NULL Current value – NULL Proxy information – original login name, if set proxy is in effect or replace – for create or replace