|
|
Transact-SQL User's Guide |
The complete syntax for create procedure is:
create procedure [owner.]procedure_name[;number]
[[(]@parameter_name
datatype [(length) | (precision [, scale])]
[= default][output]
[, @parameter_name
datatype [(length) | (precision [, scale])]
[= default][output]]...[)]]
[with recompile]
as {SQL_statements | external name dll_name}
You can create a procedure in the current database only.
Permission to issue create procedure defaults to the Database Owner, who can transfer it to other users.
Here is the complete syntax statement for execute:
[exec[ute]] [@return_status = ]
[[[server.]database.]owner.]procedure_name[;number]
[[@parameter_name =] value |
[@parameter_name =] @variable [output]
[,[@parameter_name =] value |
[@parameter_name =] @variable [output]...]]
[with recompile]
Note: When Component Integration Services is not enabled, remote procedure calls (RPCs) are not treated as part of a transaction. If you execute an RPC after begin transaction, and then execute rollback transaction, Adaptive Server does not roll back any changes made by the RPC on remote data. The stored procedure designer should be sure that all conditions that might trigger a rollback are checked before issuing an RPC that will alter remote data. If Component Integration Services is enabled, use the set transactional rpc and cis rpc handling commands to use transactional RPCs. For more information on the transactional rpc and cis rpc handling options, see the set command in the Adaptive Server Reference Manual.
A parameter is an argument to a stored procedure. One or more parameters can optionally be declared in a create procedure statement. The value of each parameter named in a create procedure statement must be supplied by the user when the procedure is executed.
Parameter names must be preceded by an @ sign and must conform to the rules for identifiers, as discussed under "Identifiers". Parameter names are local to the procedure that creates them; the same parameter names can be used in other procedures. Enclose any parameter value that includes punctuation (such as an object name qualified by a database name or owner name) in single or double quotes. Parameter names, including the @ sign, can be a maximum of 30 bytes long.
Parameters must be given a system datatype (except text or image) or a user-defined datatype, and (if required for the datatype) a length or precision and scale in parentheses.
Here is a stored procedure for the pubs2 database. Given an author's last and first names, the procedure displays the names of any books written by that person and the name of each book's publisher.
create proc au_info @lastname varchar(40),
@firstname varchar(20) as
select au_lname, au_fname, title, pub_name
from authors, titles, publishers, titleauthor
where au_fname = @firstname
and au_lname = @lastname
and authors.au_id = titleauthor.au_id
and titles.title_id = titleauthor.title_id
and titles.pub_id = publishers.pub_id
Now, execute au_info:
au_info Ringer, Anne
au_lname au_fname title pub_name
-------- -------- --------------------- ----------
Ringer Anne The Gourmet Microwave Binnet & Hardley
Ringer Anne Is Anger the Enemy? New Age Books
(2 rows affected, return status = 0)
The following stored procedure queries the system tables. Given a table name as the parameter, the procedure displays the table name, index name, and index ID.
create proc showind @table varchar(30) as
select table_name = sysobjects.name,
index_name = sysindexes.name, index_id = indid
from sysindexes, sysobjects
where sysobjects.name = @table
and sysobjects.id = sysindexes.id
The column headings, for example, table_name, were added to improve the readability of the results. Here are acceptable syntax forms for executing this stored procedure:
execute showind titles
exec showind titles
execute showind @table = titles
execute GATEWAY.pubs2.dbo.showind titles
showind titles
The last syntax form, without exec or execute, is acceptable as long as the statement is the only one or the first one in a batch.
Here are the results of executing showind in the pubs2 database when titles is given as the parameter:
table_name index_name index_id
---------- ---------- ----------
titles titleidind 0
titles titleind 2
(2 rows affected, return status = 0)
If you supply the parameters in the form "@parameter = value" you can supply them in any order. Otherwise, you must supply parameters in the order of their create procedure statement. If you supply one value in the form "@parameter = value", then supply all subsequent parameters this way.
The following procedure shows the datatype of any column. Here, the procedure displays the datatype of the qty column from the salesdetail table.
create procedure showtype @tabname varchar(18), @colname varchar(18) as
select syscolumns.name, syscolumns.length,
systypes.name
from syscolumns, systypes, sysobjects
where sysobjects.id = syscolumns.id
and @tabname = sysobjects.name
and @colname = syscolumns.name
and syscolumns.type = systypes.type
When the procedure is executed, the @tabname and @colname values can be given in a different order from the create procedure statement if they are specified by name:
exec showtype
@colname = qty , @tabname = salesdetail
You can use case expressions in any stored procedure where you use a value expression. The following example checks the sales for any book in the titles table:
create proc booksales @titleid tid
as
select title, total_sales,
case
when total_sales != null then "Books sold"
when total_sales = null then "Book sales not available"
end
from titles
where @titleid = title_id
For example:
booksales MC2222
title
total_sales
------------------------
-----------
Silicon Valley Gastronomic Treats 2032 Books
sold
(1 row affected)You can assign a default value for the parameter in the create procedure statement. This value, which can be any constant, is taken as the argument to the procedure if the user does not supply one.
Here is a procedure that displays the names of all the authors who have written a book published by the publisher given as a parameter. If no publisher name is supplied, the procedure shows the authors published by Algodata Infosystems.
create proc pub_info
@pubname varchar(40) = "Algodata Infosystems" as
select au_lname, au_fname, pub_name
from authors a, publishers p, titles t, titleauthor ta
where @pubname = p.pub_name
and a.au_id = ta.au_id
and t.title_id = ta.title_id
and t.pub_id = p.pub_id
Note that if the default value is a character string that contains embedded blanks or punctuation, it must be enclosed in single or double quotes.
When you execute pub_info, you can give any publisher's name as the parameter value. If you do not supply any parameter, Adaptive Server uses the default, Algodata Infosystems.
exec pub_info
au_lname au_fname pub_name
-------------- ------------
--------------------
Green Marjorie Algodata
Infosystems
Bennet Abraham Algodata
Infosystems
O'Leary Michael Algodata
Infosystems
MacFeather Stearns Algodata
Infosystems
Straight Dick Algodata
Infosystems
Carson Cheryl Algodata
Infosystems
Dull Ann Algodata
Infosystems
Hunter Sheryl Algodata
Infosystems
Locksley Chastity Algodata Infosystems
(9 rows affected, return status = 0)You assign "titles" as the default value for the @table parameter in this procedure, showind2:
create proc showind2
@table varchar(30) = titles as
select table_name = sysobjects.name,
index_name = sysindexes.name, index_id = indid
from sysindexes, sysobjects
where sysobjects.name = @table
and sysobjects.id = sysindexes.id
The column headings, for example, table_name, clarify the results display. Here is what the procedure shows for the authors table:
showind2 authors
table_name index_name index_id
----------- ------------- ---------
authors auidind 1
authors aunmind 2
(2 rows affected, return status = 0)
If the user does not supply a value, Adaptive Server uses the default, titles.
showind2
table_name index_name index_id
----------- ----------- ---------
titles titleidind 1
titles titleind 2
(2 rows affected, return status =0)
If a parameter is expected but none is supplied, and a default value is not supplied in the create procedure statement, Adaptive Server displays an error message listing the parameters expected by the procedure.
If a user executes a stored procedure and specifies more parameters than the number of parameters expected by the procedure, Adaptive Server ignores the extra parameters.
In the create procedure statement, you can declare NULL as the default value for individual parameters:
create procedure procedure_name
@param datatype [ = null ]
[, @param datatype [ = null ]]...
In this case, if the user does not supply a parameter, Adaptive Server executes the stored procedure without displaying an error message.
The procedure definition can specify an action be taken if the user does not give a parameter, by checking to see that the parameter's value is null. Here is an example:
create procedure showind3
@table varchar(30) = null as
if @table is null
print "Please give a table name."
else
select table_name = sysobjects.name,
index_name = sysindexes.name,
index_id = indid
from sysindexes, sysobjects
where sysobjects.name = @table
and sysobjects.id = sysindexes.id
If the user fails to give a parameter, Adaptive Server prints the message from the procedure on the screen.
For other examples of setting the default to NULL, examine the source text of system procedures using sp_helptext.
The default can include the wildcard characters (%, _, [] , and [^]) if the procedure uses the parameter with the like keyword.
For example, showind can be modified to display information about the system tables if the user does not supply a parameter, like this:
create procedure showind4
@table varchar(30) = "sys%" as
select table_name = sysobjects.name,
index_name = sysindexes.name,
index_id = indid
from sysindexes, sysobjects
where sysobjects.name like @table
and sysobjects.id = sysindexes.id
Here is a variant of the stored procedure au_info that has defaults with wildcard characters for both parameters:
create proc au_info2
@lastname varchar(30) = "D%",
@firstname varchar(18) = "%" as
select au_lname, au_fname, title, pub_name
from authors, titles, publishers, titleauthor
where au_fname like @firstname
and au_lname like @lastname
and authors.au_id = titleauthor.au_id
and titles.title_id = titleauthor.title_id
and titles.pub_id = publishers.pub_id
If au_info2 is executed with no parameters, all the authors with last names beginning with "D" are displayed:
au_info2
au_lname au_fname title pub_name
-------- ------- ------------------------- -------------
Dull Ann Secrets of Silicon Valley Algodata Infosystems
DeFrance Michel The Gourmet Microwave Binnet & Hardley
(2 rows affected)
If defaults are available for parameters, parameters can be omitted at execution, beginning with the last parameter. You cannot skip a parameter unless NULL is its supplied default.
Note: If you supply parameters in the form @parameter = value, you can supply parameters in any order. You can also omit a parameter for which a default has been supplied. If you supply one value in the form @parameter = value, then supply all subsequent parameters this way.
As an example of omitting the second parameter when defaults for two parameters have been defined, you can find the books and publishers for all authors with the last name "Ringer" like this:
au_info2 Ringer
au_lname au_fname title Pub_name
-------- -------- --------------------- ------------
Ringer Anne The Gourmet Microwave Binnet & Hardley
Ringer Anne Is Anger the Enemy? New Age Books
Ringer Albert Is Anger the Enemy? New Age Books
Ringer Albert Life Without Fear New Age Books
(4 rows affected)
If a user executes a stored procedure and specifies more parameters than the number of parameters expected by the procedure, Adaptive Server ignores the extra parameters.
Remember that SQL is a free-form language. There are no rules about the number of words you can put on a line or where you must break a line. If you issue a stored procedure followed by a command, Adaptive Server will attempt to execute the procedure and then the command. For example, if you issue the following commands:
sp_help checkpoint
Adaptive Server returns the output from sp_help and runs the checkpoint command. Using delimited identifiers for procedure parameters can produce unintended results.
The optional semicolon and integer number after the name of the procedure in the create procedure and execute statements allow you to group procedures of the same name so that they can be dropped together with a single drop procedure statement.
Procedures used in the same application are often grouped this way. For example, you might create a series of procedures called orders;1, orders;2, and so on. The following statement would drop the entire group:
drop proc orders
Once procedures have been grouped by appending a semicolon and number to their names, they cannot be dropped individually. For example, the following statement is not allowed:
drop proc orders;2
To run Adaptive Server in the evaluated configuration, you must prohibit grouping of procedures. This prohibition ensures that every stored procedure has a unique object identifier and can be dropped individually. To disallow procedure grouping, a System Security Officer must reset the allow procedure grouping configuration parameter. For information, see the System Administration Guide.
In the create procedure statement, the optional clause with recompile comes just before the SQL statements. It instructs Adaptive Server not to save a plan for this procedure. A new plan is created each time the procedure is executed.
In the absence of with recompile, Adaptive Server stores the execution plan that it created. Usually, this execution plan is satisfactory.
However, a change in the data or parameter values supplied for subsequent executions may cause Adaptive Server to create an execution plan that is different from the one it created when the procedure was first executed. In such situations, Adaptive Server needs a new execution plan.
Use with recompile in a create procedure statement when you think you need a new plan. See the Adaptive Server Reference Manual for more information.
In the execute statement, the optional clause with recompile comes after any parameters. It instructs Adaptive Server to compile a new plan, which is used for subsequent executions.
Use with recompile when you execute a procedure if your data has changed a great deal, or if the parameter you are supplying is atypical that is, if you have reason to believe that the plan stored with the procedure might not be optimal for this execution of it.
Note: If you use select * in your create procedure statement, the procedure, even if you use the with recompile option to execute, does not pick up any new columns added to the table. You must drop the procedure and recreate it.
Nesting occurs when one stored procedure or trigger calls another. The nesting level is incremented when the called procedure or trigger begins execution and it is decremented when the called procedure or trigger completes execution. Exceeding the maximum of 16 levels of nesting causes the procedure to fail. The current nesting level is stored in the @@nestlevel global variable.
You can call another procedure by name or by a variable name in place of the actual procedure name. For example:
create procedure test1 (@var char(10)
as exec @au_info2
You can create and use temporary tables in a stored procedure, but the temporary table exists only for the duration of the stored procedure that creates it. When the procedure completes, Adaptive Server automatically drops the temporary table. A single procedure can:
Since the temporary table must exist in order to create procedures that reference it, here are the steps to follow:
create table #tempstores
(stor_id char(4), amount money)
create procedure inv_amounts as
select stor_id, "Total Due" = sum(amount)
from #tempstores
group by stor_id
drop table #tempstores
create procedure inv_proc asWhen you run the inv_proc procedure, it creates the table, but it only exists during the procedure's execution. Try inserting values into the #tempstores table or running the inv_amounts procedure:
create table #tempstores
(stor_id char(4), amount money)
insert #tempstores
select stor_id, sum(qty*(100-discount)/100*price)
from salesdetail, titles
where salesdetail.title_id = titles.title_id
group by stor_id, salesdetail.title_id
exec inv_amounts
You cannot, because the #tempstores table no longer exists.
You can also create temporary tables without the # prefix, using create table tempdb..tablename... from inside a stored procedure. These tables do not disappear when the procedure completes, so they can be referenced by independent procedures. Follow the above steps to create these tables.
You can use some of the set command options the inside a stored procedure. The set option remains in effect during the execution of the procedure and most options revert to the former setting at the close of the procedure. Only the dateformat, datefirst, and language options do not revert to their former settings.
However, if you use a set option (such as identity_insert) that requires the user to be the object owner, a user who is not the object owner cannot execute the stored procedure.
After you create a stored procedure, the source text describing the procedure is stored in the text column of the syscomments system table. In previous releases of SQL Server, users often deleted the source text from syscomments, in order to save disk space and remove confidential information from this public area. Do not remove this information from syscomments; doing so can cause problems for future upgrades of Adaptive Server. Instead, encrypt the text in syscomments by using the sp_hidetext system procedure, described in the Adaptive Server Reference Manual. For more information, see "Compiled Objects".
You can execute stored procedures after a time delay or remotely.
The waitfor command delays execution of a stored procedure at a specified time or until a specified amount of time has passed.
For example, to execute the procedure testproc in half an hour:
begin
waitfor delay "0:30:00"
exec testproc
end
After issuing the waitfor command, you cannot use that connection to Adaptive Server until the specified time or event occurs.
You can execute procedures on a remote Adaptive Server from your local Adaptive Server. Once both servers are properly configured, you can execute any procedure on the remote Adaptive Server simply by using the server name as part of the identifier. For example, to execute a procedure named remoteproc on a server named GATEWAY:
exec gateway.remotedb.dbo.remoteproc
See the System Administration Guide for information on how to configure your local and remote Adaptive Servers for remote execution of procedures. You can pass one or more values as parameters to a remote procedure from the batch or procedure that contains the execute statement for the remote procedure. Results from the remote Adaptive Server appear on your local terminal.
The return status from procedures can be used to capture and transmit information messages about the execution status of your procedures. For more information, see "Return Status".
WARNING! If Component Integration Services is not enabled, Adaptive Server does not treat remote procedure calls (RPCs) as part of a transaction. Therefore, if you execute an RPC as part of a transaction, and then roll back the transaction, Adaptive Server does not roll back any changes made by the RPC. When Component Integration Services is enabled, use set transactional rpc and set cis rpc handling to use transactional RPCs. For more information on the transactional rpc and cis rpc handling options, see the set command in the Adaptive Server Reference Manual.
|
|