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 full 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 the System Administration Guide.
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)
