"usp_CustomerLogin" Stored Procedure

Description:

This stored procedure is involved in the authentication of customers in the Adventure Works Cycles application. It accepts the customer's email address and returns the CustomerID if the address is found. Otherwise it returns zero.

Definition:
CREATE Procedure usp_CustomerLogin
(
    @Email      nvarchar(50),
    @CustomerID int OUTPUT
)
WITH EXECUTE AS OWNER
AS

SELECT 
    @CustomerID = CustomerID
    
FROM 
    Sales.Individual AS I
JOIN Person.Contact AS C
ON I.ContactID = C.ContactID
    
WHERE 
    C.EmailAddress = @Email;

IF @@Rowcount < 1 
SELECT 
    @CustomerID = 0;

Database Tables Used:

Customer:  The Customer table keeps track of base customer information in the system. The primary key is CustomerID. It has a one to many relationship with the SalesOrderHeader table.

Individual:  The Individual table keeps track of retail customers. It contains the link between the Customer table and the Contact table for people who buy products on the web site.

Contact:  The Contact table keeps track of people. It contains information about the users' name, email address, password hash, and password salt.