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.