Second Stored Procedure

--stored procedures

--Add new Donor

--insert into person
--insert into personAddress
--insert into personContact
--insert into donation

--we could check to make sure it is an new donor

use communityAssist
go
--version one
alter proc usp_NewDonor
--add all the parameters
@lastname nvarchar(255),
@firstName nvarchar(255),
@Apartment nvarchar(255) =null,
@Street nvarchar(255),
@city nvarchar(255) = 'Seattle',
@state nvarchar(2)='Wa',
@zip nvarchar(10),
@homePhone nvarchar(255),
@email nvarchar(255),
@Donation money,
@EmployeeKey int =null
AS
Insert into Person (LastName, FirstName)
values(@Lastname, @FirstName)

Insert into PersonAddress(Street, Apartment, [State], City, Zip, PersonKey)
Values(@Street, @Apartment, @State,@city,@Zip, ident_current('Person'))

Insert into PersonContact(ContactInfo, PersonKey, ContactTypeKey)
Values(@homePhone, ident_current('Person'), 
(select contacttypeKey from ContactType where contactTypeName='home phone'))

Insert into PersonContact(ContactInfo, PersonKey, ContactTypeKey)
Values(@email, ident_current('Person'), 
(select contacttypeKey from ContactType where contactTypeName='email'))

Insert into Donation(DonationDate, DonationAmount, PersonKey, EmployeeKey)
Values(GetDate(), @Donation, ident_Current('Person'), @EmployeeKey)
go
--inserting all the parameter values
exec usp_NewDonor
@lastname='Skywalker', 
@firstName='Luke', 
@Apartment='101', 
@Street='2001 Dagoba', 
@city='Seattle', 
@state='Wa', 
@zip='98122', 
@homePhone='2065551345', 
@email='skywalker@starwars.com', 
@Donation=2000, 
@EmployeeKey=2

Select * From Person
Select * from PersonAddress
Select * From PersonContact where personkey =52
Select * from Donation

--checking if the defaults work
exec usp_NewDonor
@lastname='Skywalker', 
@firstName='Leah', 
@Street='2011 Dagoba', 
@zip='98122', 
@homePhone='2065551645', 
@email='skywalker@starwars.com', 
@Donation=1400

Select * from Person where Personkey = (Select max(personKey) from Person)
go

--procedure version two
alter proc usp_NewDonor
--add all the parameters
@lastname nvarchar(255),
@firstName nvarchar(255),
@Apartment nvarchar(255) =null,
@Street nvarchar(255),
@city nvarchar(255) = 'Seattle',
@state nvarchar(2)='Wa',
@zip nvarchar(10),
@homePhone nvarchar(255),
@email nvarchar(255),
@Donation money,
@EmployeeKey int =null
AS
--internal variable
Declare @personKey int
--begin the transaction
--must have higher scope than the try
Begin transaction
--begin the try
--all the code in the try will be "tried"
--if there is an error it will stop executing
--and go to the catch
Begin try
--test to make sure it is, in fact,
--a new donor
if not exists
 (Select p.personKey 
    from person p
    inner join personcontact pc
  on p.Personkey=pc.PersonKey
    where lastname=@lastname
    and firstname = @Firstname
    and ContactInfo = @email
    and ContactTypeKey=6)
Begin --if is true they don't exist {

Insert into Person (LastName, FirstName)
values(@Lastname, @FirstName)
--assign the new personkey to the variable we declared
--at the start of the procedure
Set @Personkey=ident_current('Person');

Insert into PersonAddress(Street, Apartment, [State], City, Zip, PersonKey)
Values(@Street, @Apartment, @State,@city,@Zip, @Personkey)

Insert into PersonContact(ContactInfo, PersonKey, ContactTypeKey)
Values(@homePhone, @PersonKey, 
(select contacttypeKey from ContactType where contactTypeName='home phone'))

Insert into PersonContact(ContactInfo, PersonKey, ContactTypeKey)
Values(@email, @PersonKey, 
(select contacttypeKey from ContactType where contactTypeName='email'))

End--}
Else--if the person does exist
Begin
--get the existing person's person key
Select @personkey=p.personKey 
    from person p
    inner join personcontact pc
  on p.Personkey=pc.PersonKey
    where lastname=@lastname
    and firstname = @Firstname
    and ContactInfo = @email
    and ContactTypeKey=6
end
--Now insert the donation whether they are a new
--donor or not
Insert into Donation(DonationDate, DonationAmount, PersonKey, EmployeeKey)
Values(GetDate(), @Donation, @PersonKey, @EmployeeKey)
--if there are no errors commit the transaction
--and write the inserts
Commit tran
end try
Begin Catch
--if there is an error rollback the transaction
--and undo any inserts before the error
Rollback tran
--show an error message
print error_message()
print 'there was an error. The insertion was rolled back'
End Catch

--this will cause an error because the employeekey 16
--does not exist
exec usp_NewDonor
@lastname='Brown', 
@firstName='Luke', 
@Apartment='101', 
@Street='2001 North Streets', 
@city='Seattle', 
@state='Wa', 
@zip='98122', 
@homePhone='2065551245', 
@email='lb@gmail.com', 
@Donation=450, 
@EmployeeKey=16

Select * from Person
Select * From Donation