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

create – creates a new procedure.
or replace – if the specified procedure does not exist, a new procedure is created. If the procedure does exist, the procedure definition is changed; existing permissions, auditing options, and replication attributes are preserved. Previously granted privileges on a replaced procedure are preserved.
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.
If the procedure is being replaced, the name of the procedure remains the same and the object identifier of the procedure in all the respective catalogs remains the same.
;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 SAP ASE 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.
If or replace is not specified and the procedure exists, SAP ASE raises an error that the procedure has already been created with that group number, and you must create the procedure with a different group number. If or replace clause is specified, but the group number is not, but the procedure exists with different group numbers, an error is raised because the procedure is part of a group and it cannot be replaced. You cannot specify the group number to replace an existing procedure with that group number. This is similar to the dropping of procedures where individual procedures within a group cannot be dropped.
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.

You can change the name and number of parameters when the procedure definition is replaced.
datatype[(length) | (precision[, scale])] – is the datatype of the parameter. See User-Defined Datatypes in 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, the SAP ASE server truncates the parameter value to 1 character.

The float datatype expects a binary precision in parentheses. If you omit the precision, the SAP ASE 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, the SAP ASE server uses a default precision of 18 and a scale of 0.

You can use or replace to change the type, length, precision and scale of the parameters.
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).

You can use or replace to change the default to NULL for the parameters, or set a different value when the procedure is replaced.
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.

You can use or replace to change the return parameter of the procedure.
with recompile – means that the SAP ASE 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.
If an existing procedure has been created with this option, then it can be changed using the or replace clause so that SAP ASE does not create a new plan each time the procedure is executed. If the existing procedure has not been created using with recompile, then it can be replaced with the new definition so that the plan is created each time the procedure is executed.
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.
An existing procedure’s with execute as clause can be changed from owner to caller and vice versa. You can also re-create a procedure without the with execute as clause using the or replace clause.
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.

You can change the body of the procedure to contain statements different from those in the existing procedure.
external name – creates an extended stored procedure. You cannot use the number parameter with as external name.
Extended stored procedures can also be replaced.
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.
The name of the dynamic linked library that implements the extended stored procedures can be changed.

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 – 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 – 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 – 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
```
- The value of the return parameter is always reported, whether or not its value has changed.
- @result does not need to be declared in the calling batch because it is the name of a parameter to be passed to mathtutor.
- Although the changed value of @result is returned to the caller in the variable assigned in the execute statement (in this case, @guess), it appears under its own heading (@result).
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) 
 (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 fails:
```
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 requires 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. The SAP ASE server searches for a table named t1 owned by Jane. If jane.t1 does not exist, the SAP ASE server looks for dbo.t1. If the SAP ASE 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. The SAP ASE server searches for a table named t1 owned by Bill. If bill.t1 does not exist, the SAP ASE server looks for dbo.t1. If the SAP ASE 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. The SAP ASE server checks that user Jane in otherdb has execute permission onjim.p_child. If jim.p_child has been created with execute as owner then p_child is 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 is executed 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. The SAP ASE 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 is 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 is executed 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
```

Example 14 – Based on a table of information about products, which is defined as:

create table Products (
    ProductID int,           
    ProductName varchar(30),           
    Discontinued varchar(10))       

create procedure ProductType 
    @product_ID int,                          
    @type char(10) output   
as   
declare @prod_name char(20)   
select @prod_name = ProductName, @type =           
    case @prod_name                  
        when 'Tee Shirt' then 'Shirt'                  
        when 'Sweatshirt' then 'Shirt'                  
        when 'Baseball Cap' then 'Hat'                  
        when 'Visor' then 'Hat'                  
        when 'Shorts' then 'Shorts'                  
        else 'UNKNOWN'           
    end   
from Products   
where ProductID = @product_ID       

select object_id("ProductType")       
-----------       
425049519

This next command replaces the ProductType procedure using the or replace clause. The parameters for Tee Shirt and Sweatshirt are updated, but the object ID of the procedure remains the same.

create or replace procedure ProductType 
    @product_ID int,                          
    @type char(10) output   
as   
declare @prod_name char(20)   
select @prod_name = ProductName, @type =           
    case @prod_name                  
        when 'Tee Shirt' then 'T Shirt'                  
        when 'Sweatshirt' then 'Long Sleeve Shirt'                  
        when 'Baseball Cap' then 'Hat'’                  
        when 'Visor' then 'Hat'                  
        when 'Shorts' then 'Shorts'                  
        else 'UNKNOWN'           
    end   
from Products   
where ProductID = @product_ID       

select object_id("ProductType")       
-----------       
425049519

Usage

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 the create procedure command, and resets to your previous setting once the procedure exits.
After a procedure is created, you can run it by issuing the execute command along with the procedure’s name and any parameters. If a procedure is the first statement in a batch, you can give its name without the keyword execute.
You can use sp_hidetext to hide the source text for a procedure, which is stored in syscomments.
When a stored procedure batch executes successfully, the SAP ASE server sets the @@error global variable to 0.
A procedure that calls a replaced procedure is recompiled when it executes. If replacing the 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.

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.

Setting	Description
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:

Information	Values
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