"usp_CustomerAdd" Stored Procedure

Description:

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.