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.

NoteFor 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, 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.

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 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.

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

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.

See also

Commands create function (SQLJ), drop procedure

System procedures sp_depends, sp_help, sp_helpjava, sp_helprotect