The syntax for creating a simple stored procedure, without special features such as parameters, 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, you must give the server name. The syntax for a remote procedure call is:
execute server_name.[database_name].[owner].procedure_name
The following examples execute the procedure namelist in the pubs2 database on the GATEWAY server:
execute gateway.pubs2..namelist gateway.pubs2.dbo.namelist exec gateway...namelist
The last example works only if pubs2 is your default database. For information on setting up remote procedure calls on Adaptive Server, see Chapter 15, “Managing Remote Servers,” in the System Administration Guide: Volume 1.
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 are displayed in the order that 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.
You can display the source text of a procedure with sp_helptext:
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)
In procedures created with deferred name resolution, the text in syscomments is stored without performing the select * expansion. After the procedures 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)
