Stored Procedures

use Automart

--stored procedures
--this is a simple stored procedure
--that is basically a parameterized view
go
Create proc usp_GetRegisteredCustomer
@CustomerID int
As
Select LastName
,FirstName
,Email
,LicenseNumber
,VehicleMake
,VehicleYear
From Person p
inner Join Customer.Vehicle v
on p.Personkey=v.PersonKey
inner join Customer.RegisteredCustomer rc
on p.Personkey=rc.PersonKey
Where p.Personkey=@CustomerId


Go
--the exec keyword is optional but a good idea
--this executes the stored procedure
exec usp_GetRegisteredCustomer @CustomerID=3

Go
--this is more elaborate stored procedure
--alter the procedure if you make changes
--the procedure checks to see if the customer
--already exists. if the customer does exist
--the procedure aborts (the return)
--otherwise it inserts the new customer
--the three insert statments are contained in
--a transaction and a try catch
--if there is no error in any of the inserts
--the transaction commits and the inserts
--are written; if there is an error, the program
--falls to the catch and the inserts are rolled
--back. this prevents fragments from being
--inserted. Either all inserts go through or
--none of them
create proc usp_AddNewCustomer
@LastName nvarchar(255),
@FirstName nvarchar(255),
@LicenseNumber nvarchar(10),
@VehicleMake Nvarchar(255),
@VehicleYear nchar(4),
@Email nvarchar(255),
@Password nvarchar(20)
As
if exists
 (Select lastName, FirstName, email 
 From Person p
 inner join customer.RegisteredCustomer rc
 on p.Personkey=rc.PersonKey
 where lastname=@LastName
 And firstname=@firstName
 And email =@Email)
Begin
Print 'The customer already exists'
Return
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, @Password, @PersonKey)
Commit tran
end try
Begin Catch
Rollback tran

End catch

--this inserts a new customer the first time
--if you try to run it again with the same
--values it will say the customer exists
--and abort
exec usp_AddNewCustomer
@LastName ='Nelson',
@FirstName ='Lonny',
@LicenseNumber ='375 XCD',
@VehicleMake ='Cadillac',
@VehicleYear='2014',
@Email ='ln@gmail.com',
@Password ='password'

--just checking
Select * From Person
Select * From Customer.Vehicle where Personkey=60
Select * From customer.RegisteredCustomer where PersonKey=60

--a stored procecure to update the license number
--of a vehicle
--if the vehicle exists it will update it
--if not it will insert a new vehicle
Go
Alter proc usp_UpdateVehicle
@LicenseNumberOld nvarchar(10),
@LicenseNumber nvarchar(10),
@VehicleMake nvarchar(255),
@VehicleYear nchar(4),
@PersonKey int
As
if exists
 (Select LicenseNumber from Customer.Vehicle
 Where LicenseNumber = @LicenseNumberOld
 And personkey =@PersonKey)
Begin

Update Customer.Vehicle
Set LicenseNumber=@LicenseNumber
Where PersonKey=@PersonKey
and VehicleMake=@VehicleMake
and VehicleYear = @VehicleYear
End

Else
Begin
Insert into Customer.Vehicle(LicenseNumber, VehicleMake, VehicleYear, PersonKey)
Values(@LicenseNumber, @VehicleMake,@VehicleYear, @PersonKey)
End


Select * From Customer.Vehicle
Go
--update a vehicle 
exec usp_UpdateVehicle
@LicenseNumberOld ='New 123',
@LicenseNumber ='DOC 123',
@VehicleMake ='Toyota Camry',
@VehicleYear ='2004',
@PersonKey =1

--get an existing value out of a column
Declare @key  int
Declare @Email nvarchar(255)
set @Email='candyman@gmail.com'
--you can use a select to get an existing value from a table
--and assign it to a variable
Select @Key = personkey from Customer.RegisteredCustomer
where Email=@Email
Select @Key
 
 Select * from customer.RegisteredCustomer