create procedure

Creates or replaces a stored procedure or an extended stored procedure (ESP) that can take one or more user-supplied parameters.

Note: For syntax and usage information about the SQLJ command for creating procedures, see create function (SQLJ).

Syntax

create [or replace] procedure [owner.]procedure_name[;number]
	[[(@parameter_name datatype [(length) | (precision [, scale])]
		[= default][output]
	[, @parameter_name datatype [(length) | (precision [, scale])]
		[= default][output]]...)]]
	[with {recompile | execute as {owner | caller}} ]
	as {SQL_statements | external name dll_name}

Parameters

Examples

Usage

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

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 create a procedure successfully even though you do not have 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. See Using Stored Procedures as Security Mechanisms in the Security Administration Guide.

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

The following describes permission checks for create procedure (also when creating an extended 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 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 for another user, you must have sa_role.

You must be the procedure owner to replace the procedure.

Auditing

Values in event and extrainfo columns of sysaudits are:

InformationValues
Event

11

Audit option

create

Command or access audited

create procedure

Information in extrainfo
  • Roles – current active roles

  • Keywords or options – For execute as owner, the procedure owner name and the keywords execute as owner are displayed. For execute as caller, the procedure caller name and keywords execute as caller are displayed.

  • Previous value – NULL

  • Current value – NULL

  • Other information – NULL

  • Proxy information – original login name, if set proxy is in effect

Related reference
create function (SQLJ)
drop procedure
execute
create view
create default
create rule
create procedure
create trigger
use
declare
if...else
while
break
continue
begin...end
goto label
return
waitfor
select
grant