Stored Procedure

--Stored procedure to process a new donation
--insert a person
--insert a address for person
--insert a home phone and an email
--insert the actual donation
---Check to make sure the person doesn't already exist
--if the do exist we still do donation, but don't enter person etc
--put all in transaction
--add a try catch
Use CommunityAssist 
go
Alter Proc usp_NewDonation
--enter parameters
@lastname nvarchar(255),
@firstname nvarchar(255),
@street nvarchar(255),
@city nvarchar(255),
@state nvarchar(2),
@zip nvarchar(10),
@homephone nvarchar(255),
@email nvarchar(255),
@donationAmount money,
@EmployeeKey int = 3
As
Begin tran
Begin try
--test to make sure the customer does not exist
--by seeing if any values in the database
--match the parameters
If not exists
  (Select lastname, firstname, ContactInfo
  From Person p
  inner join PersonAddress pa
  on p.PersonKey=pa.PersonKey
  inner join PersonContact pc
  on p.PersonKey=pc.PersonKey
  Where LastName=@lastname
  And FirstName=@firstname
  And ContactInfo=@email
  And ContactTypeKey=6)
  
 Begin --if person doesn't exist
  
  
--inserts if the person doesn't exist
Insert into Person(LastName, FirstName)
Values(@lastname, @firstname)

Declare @personKey int
--this gets the last identity created for the table in parenthesis
Set @personKey=IDENT_CURRENT('Person')

Insert into PersonAddress(Street, Apartment, [State], City, Zip, PersonKey)
Values(@street, null, @state, @city, @zip, @personKey)

Insert into dbo.PersonContact(ContactInfo, PersonKey, ContactTypeKey)
Values(@homephone, @personKey, 1)

Insert into dbo.PersonContact(ContactInfo, PersonKey, ContactTypeKey)
Values(@email, @personKey, 6)

End--end of 'if person doesn't exist'

--we need a new person key, because they may
--have an existing key
Declare @Personkeyb int

--same query as above but now the person
--does exist
Select @Personkeyb=p.PersonKey From Person p
Inner join PersonContact pc
on p.PersonKey=pc.PersonKey
Where FirstName=@firstname
And LastName=@lastname
And ContactInfo =@email
And ContactTypekey=6

--insert the donation
Insert into Donation(DonationDate, DonationAmount, PersonKey, EmployeeKey)
Values(GETDATE(), @donationAmount, @personKeyb, @EmployeeKey)

Commit tran --if it gets here commit the transaction
End try --end the try part
Begin Catch --begin the catch 
Rollback Tran --roll back the transaction (all or nothing)
--the print only works in the sql environment
print 'The insertion of the donation failed'
End Catch

--here is the test 
Go
Exec usp_NewDonation
@lastname ='John',
@firstname='Smith' ,
@street='somewhere 101',
@city ='Seattle',
@state ='WA',
@zip='98000' ,
@homephone ='2065551000',
@email ='js@yahoo.com',
@donationAmount=150,
@Employeekey=1

--selects to check on the results
Select * From Person
Select * from PersonAddress Where PersonKey=53
Select * from PersonContact Where PersonKey=53
Select * from Donation where PersonKey=53