Stored procedure Two: New Donor

--stored procedures

use CommunityAssist 

/*
Create a new a donor and donation
Check to see if person exists
Insert into Person
insert into PersonAddress
Insert into PersonContact
Insert into Donation
*/
go
--alter the existing procedure
--you can only create once
Alter proc usp_NewDonation
--define the user provided parameters
@firstname nvarchar(255),
@LastName nvarchar(255),
@Street nvarchar(255),
@Apartment nvarchar(255), 
@State nvarchar(2) = 'WA', 
@City nvarchar(255), 
@Zip nvarchar(10), 
@phone nvarchar(255),
@Email nvarchar(255),
@DonationAmount money,
@EmployeeKey int = null
AS
Begin tran --begin the transaction
--so it all happens or none of them
--declare personkey at procedure scope
Declare @personKey int 
Begin try
--test to see if donor exists
if Exists
 (Select Lastname, firstname, ContactInfo
  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 they do exist get their 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 
Else --otherwise insert a new person
Begin
Insert into Person(lastname, firstname)
Values (@LastName, @firstname)


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(@phone, @personKey, 1);

Insert into PersonContact(ContactInfo, PersonKey, ContactTypeKey)
Values(@Email, @personKey, 6);
End
--in either case take their donation
Insert into Donation(DonationDate, DonationAmount, PersonKey, EmployeeKey)
Values(GETDATE(),@DonationAmount, @personKey, @EmployeeKey);

--if all goes well commit the transaction
Commit tran
End try
--otherwise rollback the transaction and handle the error
Begin Catch
Rollback Tran
print Error_Message()
End Catch
Go

exec usp_NewDonation
@firstname ='Mom',
@LastName='Bates',
@Street='1000 north south street',
@Apartment = null, 
@State ='WA', 
@City='Seattle', 
@Zip= '98122', 
@phone ='2065551234',
@Email='psycho2@msn.com',
@DonationAmount =100.00
--@EmployeeKey =45

Select * From Person
Select * from PersonAddress where PersonKey=63
Select * from Donation

Select * From Employee