|
|
Transact-SQL User's Guide |
When you run a stored procedure, Adaptive Server prepares an execution plan so that the procedure's execution is very fast. Stored procedures can:
The ability to write stored procedures greatly enhances the power, efficiency, and flexibility of SQL. Compiled procedures dramatically improve the performance of SQL statements and batches. In addition, stored procedures on other Adaptive Servers can be executed if both your server and the remote server are set up to allow remote logins. You can write triggers on your local Adaptive Server that execute procedures on a remote server whenever certain events, such as deletions, updates, or inserts, take place locally.
Stored procedures differ from ordinary SQL statements and from batches of SQL statements in that they are precompiled. The first time you run a procedure, Adaptive Server's query processor analyzes it and prepares an execution plan that is ultimately stored in a system table. Subsequently, the procedure is executed according to the stored plan. Since most of the query processing work has already been performed, stored procedures execute almost instantly.
Adaptive Server supplies a variety of stored procedures as convenient tools for the user. Those that are stored in the sysprocedures table are called system procedures.
You create a stored procedure with the create procedure command. To execute a stored procedure, either a system procedure or a user-defined procedure, use the execute command. Or you can use the name of the stored procedure alone, as long as it is the first word in a statement or batch.
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 Chapter 7, "Managing Remote Servers," in the Security 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. Of course, 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 the system procedure 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)
Stored procedures can serve as security mechanisms, since a user can be granted permission to execute a stored procedure, even if she or he does not have permissions on the tables or views referenced in it or permission to execute specific commands. For details, see the Security Features User's Guide.
You can protect the source text of a stored procedure against unauthorized access by restricting select permission on the text column of the syscomments table to the creator of the procedure and the System Administrator. This restriction is required to run Adaptive Server in the evaluated configuration. To enact this restriction, a System Security Officer must reset the allow select on syscomments.text column parameter with the system procedure sp_configure. For more information, see the System Administration Guide.
Another way to protect access to the source text of a stored procedure is to hide the source text using the system procedure sp_hidetext. For information about hiding source text, see sp_hidetext in the Adaptive Server Reference Manual.
As a database changes, you can re-optimize the original query plans used to access its tables by recompiling them with the system procedure sp_recompile. This saves you the work of having to find, drop, and then re-create every stored procedure and trigger. This example marks every stored procedure and trigger that accesses the table titles to be recompiled the next time it is executed.
sp_recompile titles
For detailed information about sp_recompile, see the Adaptive Server Reference Manual.
|
|