This is a simple stored procedure that adds a new customer to the system. It does so by adding a row to the Customer table with the customer's full name, email and password. It returns the newly added CustomerID as an Out parameter.
Definition:CREATE Procedure usp_CustomerAdd ( @FirstName nvarchar(50), @LastName nvarchar(50), @Email nvarchar(50), @PasswordHash varchar(40), @PasswordSalt varchar(10), @CustomerID int OUTPUT ) WITH EXECUTE AS OWNER AS DECLARE @ContactID int DECLARE @err int BEGIN TRANSACTION; INSERT INTO Person.Contact ( FirstName, LastName, EmailAddress, PasswordHash, PasswordSalt ) VALUES ( @FirstName, @LastName, @Email, @PasswordHash, @PasswordSalt ) SELECT @ContactID = SCOPE_IDENTITY(), @err = @@error; IF (@err <> 0) GOTO abort; INSERT INTO Sales.Customer ( CustomerType ) VALUES ( N'I' -- An individual ); SELECT @CustomerID = SCOPE_IDENTITY(), @err = @@error; IF (@err <> 0) GOTO abort; INSERT INTO Sales.Individual ( ContactID, CustomerID ) VALUES ( @ContactID, @CustomerID ); SELECT @err = @@error; IF (@err <> 0) GOTO abort; COMMIT TRANSACTION; RETURN(0); abort: ROLLBACK TRANSACTION; RETURN(@err);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.