To create a simple stored procedure, without special features such as parameters, the syntax is:
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. See “Restrictions associated with stored procedures”. A procedure can be as simple as a single statement that lists the names of all the users in a database:
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 Adaptive Server 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 Adaptive Server, include the server name. The syntax for a remote procedure call is:
execute server_name.[database_name].[owner].procedure_name
See “Executing procedures remotely”.
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 Adaptive Server. Use sp_hidetext to encrypt the text in syscomments. See the Reference Manual: Procedures and “Compiled objects”.
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, Adaptive Server 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 ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ /* Adaptive Server 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)
