create procedure (SQLJ)

Creates or replaces a SQLJ stored procedure by adding a SQL wrapper to a Java static method. Accepts user-supplied parameters and return result sets and output parameters.

Note: For syntax and usage information about the Transact-SQL command for creating procedures, see create procedure.

Syntax

create [or replace] procedure [owner.]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
		...]])]'

Parameters

create – creates a new SQLJ procedure.
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 – is the Transact-SQL name of the procedure, which must conform to the rules for identifiers and cannot be a variable. Specify the owner’s name to create another procedure of the same name owned by a different user in the current database. The default value for owner is the current user.
Use or replace to change the name and number of parameters.
in | out | inout – specifies the mode of the listed parameter. in indicates an input parameter; out indicates an output parameter; and inout indicates a parameter that is both an input and an output parameter. The default mode is in.
Use or replace to change the mode of the listed parameter.
sql_parameter_name – is the name of an argument to the procedure. The value of each input parameter is supplied when the procedure is executed. Parameters are optional; a SQLJ stored procedure need not take arguments.
Parameter names must conform to the rules for identifiers. If the value of a parameter contains nonalphanumeric characters, it must be enclosed in quotes. This includes object names qualified by a database name or owner name, since they include a period. If the value of the parameter begins with a numeric character, it also must be enclosed in quotes.
sql_datatype [(length) | (precision [, scale])] – is the Transact-SQL datatype of the parameter.

sql_datatype is the SQL procedure signature.

Use or replace to change the type, length, precision, and scale of the parameters.
default – defines a default value for the procedure’s parameter. If a default is defined, you can execute the procedure without a parameter value. The default must be a constant. It can include wildcard characters (%, _, [ ], and ^) if the procedure uses the parameter name with the keyword like.
The default can be NULL. The procedure definition can specify some action to be taken if the parameter value is NULL.

Use or replace to change the default to NULL for the parameters, or set a different value when the procedure is changed.
modifies sql data – indicates that the Java method invokes SQL operations, reads, and modifies SQL data in the database. This is the default and only implementation. It is included for syntactic compatibility with the ANSI standard.
dynamic result sets integer – specifies that the Java method can return SQL result sets. integer specifies the maximum number of result sets the method can return. This value is implementation-defined.
deterministic | not deterministic – this syntax is supported for compatibility with other SQLJ-compliant vendors.
Use or replace to change the deterministic value.
language java – specifies that the external routine is written in Java. This is a required clause for SQLJ stored procedures.
parameter style java – specifies that the parameters passed to the external routine at runtime are Java parameters. This is a required clause for SQLJ stored procedures.
external – indicates that create procedure defines a SQL name for an external routine written in a programming language other than SQL.
Use or replace to change the name of the external routine.
name – specifies the name of the external routine (Java method). The specified name is a character-string literal and must be enclosed in single quotes:
```
'java_method_name [ java_datatype
                  [{, java_datatype} ...]]'
```
java_method_name – specifies the name of the external Java method.
Use or replace to change the Java method name.
java_datatype – specifies a Java datatype that is mappable or result-set mappable. This is the Java method signature.
Use or replace to change the Java datatype.

Examples

Example 1 – Creates the SQLJ procedure java_multiply, which multiplies two integers and returns an integer.

create procedure java_multiply (param1 integer,
      param2 integer, out result integer)
    language java parameter style java
    external name 'MathProc.multiply'

Example 2 – Returns values that are always larger than 10:

create procedure my_max (a int = 10, b int = 10)
language java parameter style java
external name 'java.lang.Math.max'

exec my_max
 (return status = 10)

exec my_max 8
 (return status = 10)

See also the examples for Transact-SQL create procedure.

Example 3 – 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)'

Usage

The SQLJ create procedure syntax differs from the Transact-SQL create procedure syntax for compatibility with the SQLJ ANSI standard. The SAP ASE server executes each type of stored procedure in the same way.
To avoid seeing unexpected results due to changes in settings, run set rowcount 0 as your initial statement before executing create procedure. The scope of set is limited to just your create procedure command, and resets to your previous setting once the procedure exits.
You can include a maximum of 31 in, inout, and out parameters in a create procedure statement.
To comply with the ANSI standard, do not precede parameter names with the @ sign. When executing a SQLJ stored procedure from isql or other non-Java client, however, you must precede parameter names with the @ sign, which preserves the naming order.
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.

See also sp_depends, sp_help, sp_helpjava, sp_helprotect in Reference Manual: Procedures.

Permissions

When you create a procedure, no permission checks are made on objects, such as tables and views, that are referenced by the procedure. Therefore, you can successfully create a procedure without having access to its objects. All permission checks occur when a user executes the procedure.

When the procedure is executed, permission checks on objects depend upon whether the procedure and all referenced objects are owned by the same user.

If the procedure’s objects are owned by different users, the invoker must have been granted direct access to the objects. For example, if the procedure performs a select from a table that the user cannot access, the procedure execution fails.
If a procedure and its objects are owned by the same user, special rules apply. The invoker automatically has “implicit permission” to access the procedure’s objects even though the invoker could not access them directly. Without having to grant users direct access to your tables and views, you can give them restricted access with a stored procedure. In this way, a stored procedure can be a security mechanism. For example, invokers of the procedure might be able to access only certain rows and columns of your table.

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

The following describes permission checks for create procedure that differ based on your granular permissions settings.

Setting	Description
Enabled	When granular permissions is enabled, you must have the `create procedure` privilege to create a procedure. To create a procedure (SQLJ) for another user, you must have the `create any procedure` privilege. You must be the procedure owner to replace the procedure.
Disabled	With granular permissions disabled, you must be the database owner, a user with sa_role, or have the `create procedure` privilege to create a procedure. To create a procedure (SQLJ) for another user, you must have sa_role. You must be the procedure owner to replace the procedure.

Auditing

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