Java Reference
In-Depth Information
place of table names, column names, or the names of other database objects. They
can only be used to pass values to and from the stored procedure.
In addition to the variable name, you must specify a data type. All data types can be
used as a parameter for a stored procedure. You can also specify a default value for
the variable, as shown in the example.
If you want to return a value to the caller, you must specify the variable used for the
return value using the OUTPUT keyword. You can then set this value in the body of
the stored procedure.
The AS keyword is used to identify the start of the SQL statement forming the body of
the stored procedure. A very simple stored procedure with no parameter variables
might look like:
CREATE PROCEDURE LIST_ORDERS_BY_STATE
AS
SELECT
o.Order_Number,
c.Last_Name + ', ' + c.First_Name AS Name,
c.State
FROM Customers c,Orders o
WHERE c.Customer_Number = o.Customer_Number
ORDER BY c.State,c.Last_Name;
To execute this stored procedure, you simply invoke it by name. The following code
snippet shows how:
LIST_ORDERS_BY_STATE;
The stored procedure will return a result set which looks like:
Order_Number
Name
State
5
Adams, Kay
NJ
4
Corleone, Vito
NJ
2
Corleone, Fredo
NY
3
Corleone, Francis
NY
Using Input Parameters in a Stored Procedure
The following code snippet shows how you can use input parameters in a stored
procedure. This particular store procedure was designed to handle the input from an
