create procedure

Previously granted privileges on a replaced procedure are preserved.

Syntax

Changes are in bold.

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

Parameter Changes for create or replace procedure

create – if only create is specified, a new procedure is created.
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.
procedure_name – 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 – 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 – you can change the name and number of parameters when the procedure definition is replaced.
datatype [(length) (precision[,scale])] – you can change the type, length, precision and scale of the parameters.
default – you can change the default to NULL for the parameters, or set a different value when the procedure is replaced.
output – you can change the return parameter of the procedure.
with recompile – 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 – 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.
SQL_statements – you can change the body of the procedure to contain statements different from those in the existing procedure.
external name – extended stored procedures can also be replaced.
dll_name – the name of the dynamic linked library that implements the extended stored procedures can be changed.

Example

This example is based on a table of information about products, 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

Objects Dependent on Replaced Procedures

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.

Permission Changes for create or replace procedure

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

Changes for replacing a procedure are in bold.

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. You must be the procedure owner to replace the procedure.
Granular permissions disabled	With granular permissions disabled, you must be the database owner or have the `create procedure` privilege. You must be the procedure owner to replace the procedure.

Auditing Changes for create or replace procedure

Changes are in bold.

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