create procedure

Description

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

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

Syntax

create 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

procedure_name

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

;number

is an optional integer used to group procedures that share the same name so they can be dropped together with a single drop procedure statement. Procedures used in the same application are often grouped this way. For example, if the procedures used with the application named orders are named orderproc;1, orderproc;2, and so on, the following statement drops the entire group:

 drop proc orderproc

Once procedures have been grouped, individual procedures within the group cannot be dropped. For example, the following statement is not allowed:

 drop procedure orderproc;2

You cannot group procedures if you are running Adaptive Server in the evaluated configuration. The evaluated configuration requires that you disallow procedure grouping so that every stored procedure has a unique object identifier and can be dropped individually. To disallow procedure grouping, a system security officer must use sp_configure to reset allow procedure grouping. For more information about the evaluated configuration, see the System Administration Guide.

parameter_name

is the name of an argument to the procedure. The value of each parameter is supplied when the procedure is executed. Parameter names are optional in create procedure statements—a procedure is not required to take any arguments.

Parameter names must be preceded by the @ sign and conform to the rules for identifiers. A parameter name, including the @ sign, can be a maximum of 30 characters, and larger for identifiers. Parameters are local to the procedure: the same parameter names can be used in other procedures.

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 a character parameter begins with a numeric character, it also must be enclosed in quotes.

datatype[(length) | (precision [, scale])]

is the datatype of the parameter. See “User-defined datatypes” on page 47 in Chapter 1, “System and User-Defined Datatypes”of Reference Manual: Building Blocks. Stored procedure parameters cannot have a datatype of text, unitext, or image or a user-defined datatype whose underlying type is text, unitext, or image.

The char, varchar, unichar, univarchar, nchar, nvarchar, binary, and varbinary datatypes should include a length in parentheses. If you omit the length, Adaptive Server truncates the parameter value to 1 character.

The float datatype expects a binary precision in parentheses. If you omit the precision, Adaptive Server uses the default precision for your platform.

The numeric and decimal datatypes expect a precision and scale, enclosed in parentheses and separated by a comma. If you omit the precision and scale, Adaptive Server uses a default precision of 18 and a scale of 0.

default

defines a default value for the procedure’s parameter. If a default is defined, a user can execute the procedure without giving 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 (see Example 2).

The default can be NULL. The procedure definition can specify that some action be taken if the parameter value is NULL (see Example 3).

output

indicates that the parameter is a return parameter. Its value can be returned to the execute command that called this procedure. Use return parameters to return information to the calling procedure.

To return a parameter value through several levels of nested procedures, each procedure must include the output option with the parameter name, including the execute command that calls the highest level procedure.

The output keyword can be abbreviated to out.

with recompile

means that Adaptive Server creates a new plan each time the procedure is executed. Use this optional clause when you expect that the execution of a procedure is atypical—that is, when you need a new plan. The with recompile clause has no impact on the execution of an extended stored procedure.

with execute as

specifies whether to execute the procedure as the owner or the caller. When executed as the owner, all actions in the procedure are checked against the privileges of the procedure owner. When executed as the caller, all actions in the procedure are checked against the privileges of the procedure caller.

owner

checks runtime permissions, executes DDL, and resolves objects names on behalf of the procedure owner. execute as definer is also supported as an alternative syntax.

caller

checks runtime permissions, executes DDL, and resolves objects names on behalf of the procedure caller. execute as invoker is also supported as an alternative syntax.

SQL_statements

specifies the actions the procedure is to take. You can include any number and kind of SQL statements, with the exception of create view, create default, create rule, create procedure, create trigger, and use.

create procedure SQL statements often include control-of-flow language, including one or more of the following: declare; if...else; while; break; continue; begin...end; goto label; return; waitfor; /* comment */. They can also refer to parameters defined for the procedure.

The SQL statements can reference objects in another database, as long as they are properly qualified.

external name

creates an extended stored procedure. You cannot use the number parameter with as external name.

dll_name

specifies the name of the dynamic link library (DLL) or shared library containing the functions that implement the extended stored procedure. The dll_name can be specified with no extension or with a platform-specific extension, such as .dll on Windows NT or .so on Sun Solaris. If you specify the extension, enclose the entire dll_name in quotation marks.

Examples

Example 1

Given a table name, the procedure showind displays its name and the names and identification numbers of any indexes on any of its columns:

create procedure showind @tabname varchar (30) 
as 
  select sysobjects.name, sysindexes.name, indid 
  from sysindexes, sysobjects 
  where sysobjects.name = @tabname 
  and sysobjects.id = sysindexes.id

Here are the acceptable syntax forms for executing showind:

execute showind titles
execute showind @tabname = "titles"

Or, if this is the first statement in a file or batch:

showind titles

Example 2

This procedure displays information about the system tables if the user does not supply a parameter:

create procedure 
showsysind @table varchar (30) = "sys%" 
as 
  select sysobjects.name, sysindexes.name, indid 
  from sysindexes, sysobjects 
  where sysobjects.name like @table 
  and sysobjects.id = sysindexes.id

Example 3

This procedure specifies an action to be taken if the parameter is NULL (that is, if the user does not give a parameter):

create procedure 
showindnew @table varchar (30) = null 
as 
  if @table is null 
   print "Please give a table name" 
  else 
   select sysobjects.name, sysindexes.name, indid 
   from sysindexes, sysobjects 
   where sysobjects.name = @table 
   and sysobjects.id = sysindexes.id

Example 4

This procedure multiplies two integer parameters and returns the product in the output parameter, @result:

create procedure mathtutor @mult1 int, @mult2 int, 
  @result int output 
as 
select @result = @mult1 * @mult2

If the procedure is executed by passing it three integers, the select statement performs the multiplication and assigns the values, but does not print the return parameter:

mathtutor 5, 6, 32
 (return status 0)

Example 5

Both the procedure and the execute statement include output with a parameter name so that the procedure can return a value to the caller:

declare @guess int 
select @guess = 32 
exec mathtutor 5, 6, @result = @guess output 
 (1 row affected) 
 (return status = 0) 
 
Return parameters: 
 
@result
-----------
        30

The output parameter and any subsequent parameters in the execute statement, @result, must be passed as:

@parameter = value

Example 6

You can use return parameters in additional SQL statements in the batch or calling procedure. This example shows how to use the value of @guess in conditional clauses after the execute statement by storing it in another variable name, @store, during the procedure call. When return parameters are used in an execute statement that is part of a SQL batch, the return values are printed with a heading before subsequent statements in the batch are executed.

declare @guess int 
declare @store int 
select @guess = 32 
select @store = @guess 
execute mathtutor 5, 6, @result = @guess output 
select Your_answer = @store, Right_answer = @guess 
if @guess = @store 
    print "Right-o" 
else 
    print "Wrong, wrong, wrong!"
 (1 row affected) 
 (1 row affected) 
 (return status = 0) 

Return parameters: 
 
@result
-----------
        30 
Your_answer Right_answer
----------- ------------
         32           30

 (1 row affected) 
Wrong, wrong, wrong!

Example 7

Creates an extended stored procedure named xp_echo, which takes an input parameter, @in, and echoes it to an output parameter, @out. The code for the procedure is in a function named xp_echo, which is compiled and linked into a DLL named sqlsrvdll.dll:

create procedure xp_echo @in varchar (255),
        @out varchar (255) output
as external name "sqlsrvdll.dll"

Example 8

Creates a procedure using the execute as owner clause. Jane creates the procedure and Bill requires only execute permission to run the procedure. The table emp_interim is created and owned by Jane. If Jane does not have create table permission, the procedure will fail:

create procedure p_emp  
    with execute as owner as
    select * into emp_interim
    from jane.employee
grant execute on p_emp to bill

Example 9

Creates a procedure using the execute as caller clause. Jane creates the procedure and Bill requires execute permission to run the procedure. Jane owns both p_emp and jane.employee. Bill will require select permission on jane.employee. The table emp_interim is created and owned by Bill. Bill must have create table permission:

create procedure p_emp  
    with execute as caller as
    select * into emp_interim
    from jane.employee
grant execute on p_emp to bill

Example 10

Creates a procedure using the execute as owner clause with references to an object with an unqualified name. Jane creates the procedure and Bill executes the procedure. Adaptive Server will search for a table named t1 owned by Jane. If jane.t1 does not exist, Adaptive Server will look for dbo.t1. If Adaptive Server resolves t1 to dbo.t1, permission to insert into t1 must have been granted to Jane:

create procedure insert p  
    with execute as owner as
    insert t1 (c1) values (100)
grant execute on insert p to bill

Example 11

Creates a procedure using the execute as caller clause with references to an object with an unqualified name. Jane creates the procedure and Bill executes the procedure. Adaptive Server will search for a table named t1 owned by Bill. If bill.t1 does not exist, Adaptive Server will look for dbo.t1. If Adaptive Server resolves t1 to dbo.t1, permission to insert into t1 must have been granted to Bill:

create procedure insert p  
    with execute as caller as
    insert t1 (c1) values (100)
grant execute on insert p to bill

Example 12

Creates a procedure using the execute as owner clause that invokes a nested procedure in another database with a fully qualified name. Jane creates the procedure and Bill executes the procedure. The login associated with Jane resolves to user Jane in otherdb. Adaptive Server checks that user Jane in otherdb has execute permission on jim.p_child. If jim.p_child has been created with execute as owner then p_child will be executed on behalf of Jim. If jim.p_child has been created with execute as caller or without the execute as clause, then p_child will execute on behalf of Jane:

create procedure p master  
    with execute as owner 
    as exec otherdb.jim.p_child
grant execute p master to bill

Example 13

Creates a procedure using the execute as caller clause that invokes a nested procedure in another database with a fully qualified name. Jane creates the procedure and Bill executes the procedure. The login associated with Bill resolves to user Bill in otherdb. Adaptive Server checks that user Bill in otherdb has execute permission on jim.p_child. If jim.p_child has been created with execute as owner then p_child will be executed on behalf of Jim. If jim.p_child has been created with execute as caller or without the execute as clause, then p_child will execute on behalf of Bill:

create procedure p master  
    with execute as caller
    as exec otherdb.jim.p_child
grant execute on p master to bill

Usage


Restrictions


Extended stored procedures


execute as stored procedure

For information about the execute as stored procedure, see Managing User Permissions in the Security Administration Guide.


System procedures


Nested procedures


Procedure return status


Object identifiers


Temporary tables and procedures


Setting options in procedures

You can use the set command inside a stored procedure. Most set options remain in effect during the execution of the procedure, then revert to their former settings.

However, if you use a set option (such as identity_insert) which requires the user to be the object owner, a user who is not the object owner cannot execute the stored procedure.


Getting information about procedures

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.

The following describes permission checks for create procedure (also when creating an extended procedure) that differ based on your granular permissions settings.

Granular permissions enabled

When granular permissions is enabled, you must have the create procedure privilege. You must have the create any procedure privilege to run create procedure for other users.

Granular permissions disabled

With granular permissions disabled, you must be the database owner or have the create procedure privilege.

Auditing

Values in event and extrainfo columns of sysaudits are:

Event

Audit option

Command or access audited

Information in extrainfo

11

create

create procedure

  • 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

See also

Commands begin...end, break, continue, declare, drop procedure, execute, goto label, grant, if...else, return, select, waitfor, while

System procedures sp_addextendedproc, sp_helpextendedproc, sp_helptext, sp_hidetext, sp_rename, sp_help