Examples

Examples are provided to illustrate how to create and execute a stored procedure.

create procedure procedure_name 
	as SQL_statements 

Stored procedures are database objects, and their names must follow the rules for identifiers.

Any number and kind of SQL statements can be included except for create statements.

create procedure namelist 
as select name from sysusers 

To execute a stored procedure, use the keyword execute and the name of the stored procedure, or just use the procedure’s name, as long as it is submitted to SAP ASE by itself or is the first statement in a batch. You can execute namelist in any of these ways:

namelist
execute namelist
exec namelist

To execute a stored procedure on a remote SAP ASE, include the server name. The syntax for a remote procedure call is:

execute server_name.[database_name].[owner].procedure_name

The database name is optional only if the stored procedure is located in your default database. The owner name is optional only if the database owner (“dbo”) owns the procedure or if you own it. You must have permission to execute the procedure.

A procedure can include more than one statement.

create procedure showall as 
select count(*) from sysusers 
select count(*) from sysobjects 
select count(*) from syscolumns 

When the procedure is executed, the results of each command appear in the order in which the statement appears in the procedure.

showall
------------

           5
 
(1 row affected) 
 
------------ 
          88 
 
(1 row affected) 
 
------------ 
         349 
 
(1 row affected, return status = 0)

When a create procedure command is successfully executed, the procedure’s name is stored in sysobjects, and its source text is stored in syscomments.

After you create a stored procedure, the source text describing the procedure is stored in the text column of the syscomments system table. Do not remove this information from syscomments; doing so can cause problems for future upgrades of SAP ASE. Use sp_hidetext to encrypt the text in syscomments. See the Reference Manual: Procedures.

Use sp_helptext to display the source text of a procedure:

sp_helptext showall 

# Lines of Text
---------------
              1
 
(1 row affected)

text 
---------------------------------------- 
create procedure showall as 
select count(*) from sysusers 
select count(*) from sysobjects 
select count(*) from syscolumns 
 
(1 row affected, return status = 0)

When you create procedures with deferred name resolution (which lets you create a stored procedure that references objects that do not yet exist), the text in syscomments is stored without performing the select * expansion. After the procedure’s first successful execution, SAP ASE performs the select * expansion and the text for the procedure is updated with the expanded text. Since the select * expansion is executed before updating the text, the final text contains the expanded select *, as this example shows:

create table t (a int, b int)
---------set deferred_name_resolution on
---------------create proc p as select * from t
------------------sp_helptext p
------------- # Lines of Text
---------------
             1(1 row affected)
text 
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
create proc p as select * from t
(1 row affected)
(return status = 0)exec p
------------
a           b
----------- -----------(0 rows affected)
(return status = 0)sp_helptext p
------------- # Lines of Text
---------------
              1(1 row affected)
text 
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ /* SAP ASE has expanded all '*' elements in the following statement */

create proc p as select t.a, t.b from t
(1 row affected)
(return status = 0)