Full stored procedure

Here is the full stored procedure, plus a second one that returns all donor information and a query that total donation amounts. We talked about doing a stored procedure to handle logins.

I will also post a more complete descriptions of the assignments on the syllabus before thurdsay's class.

Stored Procedure for entering donors

/******************************
Allow a donor to enter a donation
If the donor does not exist in the database
Then add the donor
If the donor does exist
We update the information
either way we take the donation

adding the donor:
inserting into Person
Get the person key
Insert into address
Check if home phone is not null
if not null insert into Person contact
Check if workphone is not null
If not null insert into personcontact
Check if email not null
if not null insert into personcontact
Insert into donation

if they do exist in database
only
Enter the donation
****************************/


--the actual procedure
Alter Proc Donor.usp_RegisterDonor
--set up the parameters
@lastname nvarchar (255),
@firstname nvarchar (255),
@Street nvarchar(255),
@Apartment nvarchar(255),
@city nvarchar(255),
@state nvarchar(2) ,
@zip nvarchar(10) ,
@homephone nvarchar(255) ,
@workphone nvarchar(255),
@email nvarchar(255),
@donationAmount money
AS --beginning of procedure body
--declare internal variables
Declare @personkey int
Declare @donationDate Datetime
Set @donationDate=GETDATE()

If Exists
(Select Firstname, lastname from Person
where LastName=@lastname and FirstName=@firstname)
Begin --begin if true
--get the existing person key
Select @personkey=Personkey from Person
Where LastName=@lastname and FirstName=@firstname
End --end if true
Else--if no exists
Begin --begins the else
--insert into person

Begin tran -- beginning of transaction
Begin try
Insert into Person(FirstName, LastName)
Values(@firstname, @Lastname)

--get the new personkey
Select @personkey =ident_Current('Person')

--insert into person Address
Insert into PersonAddress(Street, Apartment, [State], City, Zip, PersonKey)
Values(@Street, @Apartment,@state,@city,@zip,@personkey)

--insert if not null
if @homephone is not null
Begin
Insert into PersonContact(ContactInfo, PersonKey, ContactTypeKey)
Values (@homephone,@personkey,1)
End

if @Workphone is not null
Begin
Insert into PersonContact(ContactInfo, PersonKey, ContactTypeKey)
Values (@Workphone,@personkey,2)
End

if @email is not null
Begin
Insert into PersonContact(ContactInfo, PersonKey, ContactTypeKey)
Values (@email,@personkey,6)
End --end if null
Commit tran
end try--of try

Begin Catch
Rollback tran
return
End catch --end catch

End --end of the else

--do this insert no matter what
Insert into Donation(DonationDate, DonationAmount, PersonKey)
Values(@donationDate, @donationAmount, @personkey)

Test for the store procedure


--test 1
exec Donor.usp_RegisterDonor
@lastname = 'Flinstone',
@firstname ='Fred',
@Street='201 Granite way',
@Apartment= null,
@city ='Bedrock',
@state ='WA',
@zip ='98188' ,
@homephone='3604441234',
@workphone='3601212555',
@email ='ff@gmail.com',
@donationAmount =500

looking to see if the procedure did as advertised
Select * from Person
Select * from PersonAddress where PersonKey=56
Select * from PersonContact where PersonKey=56
Select * from donation where PersonKey=56
Go

procedure for donation information

Create procedure donor.Owninfo
@lastname nvarchar(255)
As
Select Lastname, firstname, street, City, [State], Zip, contactInfo,
DonationDate,DonationAmount
From Person p
inner join PersonAddress pa
on p.PersonKey=pa.PersonKey
inner join PersonContact pc
on p.PersonKey=pc.PersonKey
inner join Donation d
on p.PersonKey=d.PersonKey
where LastName=@lastName

Test the procedure
exec Donor.OwnInfo 'Jetson'

A query to get the total donations for each donor

Select p.PersonKey, lastname, firstname, SUM(donationAmount) as [Total donation]
From Donation d
inner join person p
on p.personkey=d.Personkey
Group by p.PersonKey, lastname, firstname