create procedure (SQLJ)

The or replace clause allows you to replace a SQLJ procedure definitions using create procedure.

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)

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