Databases Reference
In-Depth Information
The INSERT is performed the same way each time, and the only information
you've revealed about table structure is the column values needed to add a row.
More complicated processes are also good candidates for procedures. Because
you can include control-of-flow commands, the procedure can make decisions
while it is running based on the values and other conditions it finds. However,
you should design your procedures so that each procedure accomplishes a sin-
gle task. You can also include error handling code that will let your procedure
detect and respond appropriately to errors, rather than simply failing and report-
ing the problem.
Creating Procedures
You run CREATE PROCEDURE (or CREATE PROC) to create a user stored pro-
cedure. The basic syntax for this command is as follows:
CREATE PROC[EDURE]
procedure name
[
parameter_list
]
AS
Sql_statements
The parameter list includes input and output parameters, both of which are
optional. When defining a parameter, you must supply a parameter name and
data type. Optionally, you can specify a default value to be used if the user
doesn't specify a value for the parameter, and you can identify the parameter
as an OUTPUT parameter. The SQL statement list can include most SQL com-
mands and control-of-flow statements. You can also call other procedures as
nested procedures,
which is a procedure called and executed by another pro-
cedure. When the nested procedure finishes running, control is returned to the
calling procedure.
Let's look at a simple example with four input parameters:
CREATE PROC proc_enter_cust
@CUSTNUM CHAR(4), @CUSTNAME VARCHAR(40),
@SPNUM CHAR(3), @HQCITY VARCHAR(20)
AS
INSERT CUSTOMER VALUES (@CUSTNUM, @CUSTNAME,
@SPNUM, @HQCITY)
When you run the procedure, specify values for each of the input parameters.
If you wanted to add Home Town Supply as a new customer, you might run:
proc_enter_cust '4554', 'Home Town Supply', '361', 'Chicago'
Notice that the parameters are entered in the same order as they are specified.
If you also include the parameter names, you can pass the parameters in any
order, as in the next example:
proc_enter_cust @CUSTNUM='4554', @SPNUM='361',
@HQCITY='Chicago', @CUSTNAME='Home Town Supply'
