Java Reference
In-Depth Information
HTML form. Notice that the variable names are not required to be the same as the
column names:
CREATE PROCEDURE INSERT_CONTACT_INFO
@FName VARCHAR(20), @MI CHAR(1), @LName VARCHAR(30),
@Street VARCHAR(50), @City VARCHAR(30), @ST CHAR(2),
@ZIP VARCHAR(10), @Phone VARCHAR(20), @Email VARCHAR(50)
AS
INSERT INTO CONTACT_INFO
(First_Name, MI, Last_Name,
Street, City, State, ZIP, Phone, Email)
VALUES
(@FName, @MI, @LName,
@Street, @City, @ST, @ZIP, @Phone, @Email);
The SQL statement used to call this procedure is very similar to the statement shown
in the previous example. The only difference is the use of the input parameters
obtained from the HTML form:
INSERT_CONTACT_INFO 'Charles', 'F', 'Boyer', '172 Michelin',
'Detroit', 'MI', '76543', '900-555-1234', 'charles@boyer.net'
Using Output Parameters in a Stored Procedure
Creating a stored procedure which uses output parameters is also quite
straightforward. The example shows a stored procedure which returns a validation
message whe n a UserName, Password pair is checked against a table:
CREATE PROCEDURE CHECK_USER_NAME
@UserName varchar(30),
@Password varchar(20),
@PassFail varchar(20) OUTPUT
AS
IF EXISTS(Select * From Customers
WHERE Last_Name = @UserName
AND
First_Name = @Password)
BEGIN
SELECT @PassFail = "PASS"
END
ELSE
BEGIN
SELECT @PassFail = "FAIL"
END
