Stored Procedure

--stored Procedures
/*
create a proc that adds a new donor and donation
Get all the values for the tables below
Insert into person
Insert into PersonAddress
Insert into Personcontact
Insert into Donation
all or none
*/
use CommunityAssist
Go

Alter Procedure usp_NewDonor
--get parameters
@lastName nvarchar(255),
@firstName nvarchar(255),
@street  nvarchar(255),
@apartment nvarchar(255) = null,
@city nvarchar(255) = 'Seattle',
@state nvarchar(2)='WA',
@zip nvarchar(10),
@homePhone nvarchar(255),
@email nvarchar(255),
@donationAmount money
As
--declare class level variable
Declare @personKey int
Begin Transaction
Begin Try
--check to see if person exists
if not exists
 (Select Lastname, firstName, ContactInfo
  From Person p
  inner Join PersonContact pa
  on p. PersonKey = pa.PersonKey
  where LastName=@LastName
  And Firstname=@firstname
  and Contactinfo = @Email
  and contactTypeKey=6)
Begin --begin if they don't exist
Insert into Person (Lastname, firstName)
Values (@lastName, @firstName)

--personkey is set to new identity
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, 1),
(@Email, @personKey, 6)
End -- end if true
Else --if they do exist
Begin
--get the existing person's 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 --end of they do exist
--no matter what insert donation

Insert into Donation(DonationDate, DonationAmount, PersonKey, EmployeeKey)
Values (GetDate(), @donationAmount, @personKey, null)
Commit Tran
End try
Begin Catch
print 'The transaction failed ' + error_message()
Rollback Tran
End catch

Go

Exec usp_NewDonor
@lastName ='Hathawat',
@firstName ='Ann',
@street ='111 somewhere Street',
@Apartment='101',
@City='Bellevue',
@zip ='98556',
@homePhone='2065555753',
@email ='hathawat@gmail.com',
@donationAmount =3000

Create unique index ix_email on PersonContact(contactInfo)
where ContactTypeKey=6

Select * From Person
Select * from PersonAddress
Select * From PersonContact
Select * From Donation