"usp_CustomerDetail" Stored Procedure

Description:

This stored procedure accepts a CustomerID and returns that customer's name, email address, password hash, and password salt. Rather than use a single line recordset, usp_CustomerDetail returns the items in individual output parameters. This yields a performance increase.

Definition:
CREATE Procedure usp_CustomerDetail
(
    @CustomerID int,
    @FirstName   nvarchar(50) OUTPUT,
    @LastName   nvarchar(50) OUTPUT,
    @Email      nvarchar(50) OUTPUT,
    @PasswordHash   varchar(40) OUTPUT,
	@PasswordSalt	varchar(10) OUTPUT,
	@EmailPromotion int OUTPUT

)
AS

SELECT 
    @FirstName = FirstName, 
    @LastName = LastName,
    @Email    = EmailAddress, 
    @PasswordHash = PasswordHash,
	@PasswordSalt = PasswordSalt,
	@EmailPromotion = EmailPromotion

FROM 
    Person.Contact as C
JOIN Sales.Individual as I
ON I.ContactID = C.ContactID

WHERE 
    I.CustomerID = @CustomerID;

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.