Chapter 1: Commands

create procedure (SQLJ)

Description

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

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

Syntax

create 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

sql_procedure_name: is the Transact-SQL name of the procedure. It 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.

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.

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.

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 the wildcard characters (%, _, [ ], and ^) if the procedure uses the parameter name with the keyword like.

The default can be NULL. The procedure definition can specify that some action be taken if the parameter value is NULL.

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.

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.

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.

java_datatype: specifies a Java datatype that is mappable or result-set mappable. This is the Java method signature.

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.

Usage

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.
The SQLJ create procedure syntax differs from the Transact-SQL create procedure syntax for compatibility with the SQLJ ANSI standard. Adaptive Server executes each type of stored procedure in the same way.

Permissions

create procedure permission defaults to the Database Owner, who can transfer it to other users. Permission to use a procedure must be granted explicitly with the grant command and may be revoked with the revoke command.