Stored Procedures Winter 2014

--insert a new customer into automart
--insert into person
--insert into vehicle
--insert into RegisteredCustomer
--transaction --all the inserts should happen or none of them
--should check to make sure the customer doesn't already exist

Use Automart
Go
--this version just does the inserts raw
--it is possible to insert into one or more tables
--even though it errors in another
Create proc usp_AddNewCustomer
--parameters 
@LastName nvarchar(255), 
@FirstName nvarchar(255),
@LicenseNumber nvarchar(10), 
@VehicleMake nvarchar(255), 
@VehicleYear nchar(4), 
@Email nvarchar(255),
@CustomerPassword nvarchar(20)
As
Insert into Person(Lastname, FirstName)
Values(@lastName, @firstName)

Declare @Personkey int
set @PersonKey=ident_Current('Person')

Insert into Customer.vehicle 
(LicenseNumber, VehicleMake, VehicleYear, personKey)
Values(@LicenseNumber, @VehicleMake, @VehicleYear, @PersonKey)

Insert into Customer.RegisteredCustomer
(Email, CustomerPassword, PersonKey)
Values(@Email, @CustomerPassword, @PersonKey)

Go
--test1
exec usp_AddNewCustomer
@LastName ='Nelson', 
@FirstName ='Admiral',
@LicenseNumber='FTW100' , 
@VehicleMake='Prius', 
@VehicleYear='2014', 
@Email='admiral@gmail.com',
@CustomerPassword = 'password'

Select * From Person
Select * From Customer.Vehicle
Select * From Customer.RegisteredCustomer

--version two
--this version adds a transaction and a
--try catch
Go

Alter proc usp_AddNewCustomer
--parameters 
@LastName nvarchar(255), 
@FirstName nvarchar(255),
@LicenseNumber nvarchar(10), 
@VehicleMake nvarchar(255), 
@VehicleYear nchar(4), 
@Email nvarchar(255),
@CustomerPassword nvarchar(20)
As
Begin Tran
Begin Try
Insert into Person(Lastname, FirstName)
Values(@lastName, @firstName)

Declare @Personkey int
set @PersonKey=ident_Current('Person')

Insert into Customer.vehicle 
(LicenseNumber, VehicleMake, VehicleYear, personKey)
Values(@LicenseNumber, @VehicleMake, @VehicleYear, @PersonKey)

Insert into Customer.RegisteredCustomer
(Email, CustomerPassword, PersonKey)
Values(@Email, @CustomerPassword, @PersonKey)
Commit tran
End try
Begin Catch
Rollback tran
print 'The transaction failed'
print error_message() 
end catch
Go

--version 3
--this version checks to see if the customer exists
--and then if the vehicle exists
--if the customer exists and the vehicle does not exist, insert it
--otherwise just end
--if the customer does not exist insert the person, 
--vehicle and registered customer
Alter proc usp_AddNewCustomer
--parameters 
@LastName nvarchar(255), 
@FirstName nvarchar(255),
@LicenseNumber nvarchar(10), 
@VehicleMake nvarchar(255), 
@VehicleYear nchar(4), 
@Email nvarchar(255),
@CustomerPassword nvarchar(20)
As

If exists
 (Select p.PersonKey from Person p
 inner Join Customer.Vehicle v
 on p.personkey=v.personkey
 inner join Customer.RegisteredCustomer r
 on r.PersonKey=p.Personkey
 Where lastname=@lastName
 and firstname=@firstName
 and Email = @Email)

Begin
 Declare @pKey int
 Select @pkey=p.PersonKey from Person p
 inner Join Customer.Vehicle v
 on p.personkey=v.personkey
 inner join Customer.RegisteredCustomer r
 on r.PersonKey=p.Personkey
 Where lastname=@lastName
 and firstname=@firstName
 and Email = @Email
 if not exists
  (Select LicenseNumber, VehicleMake, VehicleYear
    from Customer.Vehicle
    Where personkey=@pKey
    and LicenseNumber=@licenseNumber
    and vehicleMake=@VehicleMake
    and VehicleYear=@VehicleYear)
  Begin
  Begin try
   Insert into Customer.vehicle(LicenseNumber,VehicleMake, VehicleYear, Personkey)
   Values (@LicenseNumber, @VehicleMake, @VehicleYear, @pKey)
   return
  End Try
  Begin Catch
  print 'The license plate already exists'
  return
  End Catch
  end
  Else
  Begin
  print 'Already in the database'
  return
  end
End
Begin Tran
Begin Try
Insert into Person(Lastname, FirstName)
Values(@lastName, @firstName)

Declare @Personkey int
set @PersonKey=ident_Current('Person')

Insert into Customer.vehicle 
(LicenseNumber, VehicleMake, VehicleYear, personKey)
Values(@LicenseNumber, @VehicleMake, @VehicleYear, @PersonKey)

Insert into Customer.RegisteredCustomer
(Email, CustomerPassword, PersonKey)
Values(@Email, @CustomerPassword, @PersonKey)
Commit tran
End try
Begin Catch
Rollback tran
print 'The transaction failed'
print error_message() 
end catch

Go
exec usp_AddNewCustomer
@LastName ='Brown', 
@FirstName ='Susan',
@LicenseNumber='WEB100' , 
@VehicleMake='Tesla', 
@VehicleYear='2013', 
@Email='sb@gmail.com',
@CustomerPassword = 'password'

Select * From Customer.Vehicle

--this is a stored procedure for updating customer
--information
--it has a problem if the customer has more than
--one vehicle. I will let you solve that
Go
Create proc usp_UpdateCustomer
@LastName nvarchar(255), 
@FirstName nvarchar(255),
@LicenseNumber nvarchar(10), 
@VehicleMake nvarchar(255), 
@VehicleYear nchar(4), 
@Email nvarchar(255),
@CustomerPassword nvarchar(20),
@Personkey int
As
Begin tran
Begin try
Update Person
set LastName=@LastName,
FirstName=@firstName
Where Personkey=@personkey

--count the vehicles see if you are
--updating an existing vehicle
--or adding something entirely new
Update Customer.Vehicle
Set LicenseNumber=@LicenseNumber,
VehicleMake=@VehicleMake,
VehicleYear=@vehicleYear
Where PersonKey = @Personkey
And @licenseNumber=@LicenseNumber

Update Customer.RegisteredCustomer
set Email=@Email,
CustomerPassword=@CustomerPassword
Where personKey=@PersonKey
Commit tran
End Try
Begin Catch
Rollback tran
print 'sorry'
end catch

go
exec usp_UpdateCustomer
@LastName ='Brown-Meyers', 
@FirstName ='Susan',
@LicenseNumber='WEB100' , 
@VehicleMake='Tesla', 
@VehicleYear='2013', 
@Email='brown_meyers@gmail.com',
@CustomerPassword = 'password',
@personkey=74

Select * From Person
Select * From Customer.Vehicle
Select * from Customer.RegisteredCustomer where personkey=74