create function (SQLJ)

Syntax

Changes are in bold.

create [or replace] function 
    [owner_name.]sql_function_name
        ([ sql_parameter_name sql_datatype     
            [(length)| (precision[, scale ])]
        [[,sql_parameter_name sql_datatype     
            [(length)| (precision[, scale])]]   
        ...]])  
    returns sql_datatype   
    [(length)| (precision[, scale])]  
    [modifies sql data]   
    [returns null on null input |    called on null input]  
    [deterministic | not deterministic]  
    [exportable]  
    language java   
    parameter style java  
    external name 'java_method_name    
        [([java_datatype[, java_datatype    
        ...]])] '

Parameter Changes for create or replace function (SQLJ)

create – creates a SQLJ function if one does not already exist.
or replace – re-defines an existing function. Use this clause to change the definition of an existing user defined SQLJ function without dropping, re-creating, and regranting object privileges previously granted on the function.
sql_function_name – the name of the function remains the same, although its definition is changed.
sql_parameter_name – names and the number of parameters can be altered when the function definition is replaced.
sql_datatype – the Transact-SQL datatype of the parameter to the function can be changed.
returns sql_datatype – result datatype of the function can be changed.
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 function named sqlj_testfun.

create function sqlj_testfun (p1 int)
                      returns int
                      language java
                      parameter style java
                      external name 'UDFSample.sample(int)'

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

create or replace function sqlj_testfun (p1 int,p2 int) 
                      returns int
                      language java
                      parameter style java
                      external name 'UDFSample.sample2(int,int)'

Objects Dependent on Replaced Functions

If the replaced SQLJ function is called by another function, both functions will be recompiled when called.

If the interface of the replaced function does not match that in the calling function, then the calling function must be replaced, otherwise the calling function raises an error. You can execute sp_depends on the replaced function to check for any calling objects.

Restrictions

If a function is referenced in a computed column or functional index, it cannot be replaced.

Permission Changes for create or replace function (SQLJ)

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

Changes for replacing a function are in bold.

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

Auditing Changes for create or replace function (SQLJ)

Changes are in bold.

Event	Audit Option	Command or access audited	Information in extrainfo
97	create	create function	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