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

Examples

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.

SettingDescription
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
Related reference
create procedure
create function (SQLJ)
drop procedure