Stored Procedures

Use CommunityAssist
go
-- stored procedures
create proc usp_Donations
@PersonKey int
As
Select DonationKey, 
DonationDate, 
DonationAmount
From Donation
Where PersonKey = @PersonKey 

exec usp_Donations @PersonKey=3

Go
--add new person
--Get all the parameters for Person,
--personAddress and PersonContact
--Make sure the person is not in the database
--already
--begin a transaction
--Begin a try--
--hash their password
--insert into person
--insert into personAddress --getting the person key
--insert into personContact with key
--commit tran
--or catch the errors and rollaback

Version 1

Create proc usp_AddPerson
@PersonLastName nvarchar(255), 
@PersonFirstName nvarchar(255), 
@PersonUsername nvarchar(25), 
@PersonPlainPassword nvarchar(50), 
@Street nvarchar(255), 
@Apartment nvarchar(255) = null, 
@State nvarchar(2) = 'WA', 
@City nvarchar(255) = 'Seattle', 
@Zip nvarchar(10), 
@HomePhone nvarchar(255) = null,
@WorkPhone nvarchar(255)=null
As
Declare @Passkey int =dbo.fx_Seed()
Declare @HomePhoneType int =1
Declare @WorkPhoneType int =2
Insert into Person
(PersonLastName, PersonFirstName, PersonUsername, PersonPlainPassword, 
Personpasskey, PersonEntryDate, PersonUserPassword)
Values(@PersonLastName, @PersonFirstName,
@PersonUsername, @PersonPlainPassword,
@Passkey, GetDate(),dbo.fx_hashPass(@PassKey, @PersonPlainPassword))
Declare @PersonKey int = Ident_Current('Person')
Insert into PersonAddress(Street, Apartment, State, City, Zip, PersonKey)
Values (@Street, @Apartment,@State, @City,@Zip, @PersonKey)
Insert into PersonContact(PersonKey, ContactInfo, ContactTypeKey)
Values(@PersonKey, @HomePhone, @HomePhoneType)
Insert into PersonContact(PersonKey, ContactInfo, ContactTypeKey)
Values(@PersonKey, @WorkPhone, @WorkPhoneType)

exec Usp_AddPerson
@PersonLastName='Anderson', 
@PersonFirstName='Marty', 
@PersonUsername='MAnderson@gmail.com', 
@PersonPlainPassword='martyPass', 
@Street='1002 South Somewhere', 
 @Zip='98100', 
 @HomePhone='2065559873', 
 @WorkPhone='2065558769'

Version 2

 Go
 Alter proc usp_AddPerson
@PersonLastName nvarchar(255), 
@PersonFirstName nvarchar(255), 
@PersonUsername nvarchar(25), 
@PersonPlainPassword nvarchar(50), 
@Street nvarchar(255), 
@Apartment nvarchar(255) = null, 
@State nvarchar(2) = 'WA', 
@City nvarchar(255) = 'Seattle', 
@Zip nvarchar(10), 
@HomePhone nvarchar(255) = null,
@WorkPhone nvarchar(255)=null
As
--Declare variables
Declare @Passkey int =dbo.fx_Seed()
Declare @HomePhoneType int =6
Declare @WorkPhoneType int =2
Begin Tran --start transaction
Begin try--start Try
--insert into Person
Insert into Person
(PersonLastName, PersonFirstName, PersonUsername, PersonPlainPassword, 
Personpasskey, PersonEntryDate, PersonUserPassword)
Values(@PersonLastName, @PersonFirstName,
@PersonUsername, @PersonPlainPassword,
@Passkey, GetDate(),dbo.fx_hashPass(@PassKey, @PersonPlainPassword))
--get person key
Declare @PersonKey int = Ident_Current('Person')
--insert int personAddress
Insert into PersonAddress(Street, Apartment, State, City, Zip, PersonKey)
Values (@Street, @Apartment,@State, @City,@Zip, @PersonKey)
--insert home phone
Insert into PersonContact(PersonKey, ContactInfo, ContactTypeKey)
Values(@PersonKey, @HomePhone, @HomePhoneType)
--insert work phone
Insert into PersonContact(PersonKey, ContactInfo, ContactTypeKey)
Values(@PersonKey, @WorkPhone, @WorkPhoneType)
commit tran --all is good write it
End try
Begin catch --if an error will fall here
Rollback Tran--undo the transaction
print 'Transaction rolled back'
print Error_Message();
End Catch

exec Usp_AddPerson
@PersonLastName='Munse', 
@PersonFirstName='Nelson', 
@PersonUsername='NMunse@gmail.com', 
@PersonPlainPassword='MunsePass', 
@Street='1004 North Somewhere', 
 @Zip='98100', 
 @HomePhone='2065559773', 
 @WorkPhone='2065558729'

Third Version

  Alter proc usp_AddPerson
@PersonLastName nvarchar(255), 
@PersonFirstName nvarchar(255), 
@PersonUsername nvarchar(25), 
@PersonPlainPassword nvarchar(50), 
@Street nvarchar(255), 
@Apartment nvarchar(255) = null, 
@State nvarchar(2) = 'WA', 
@City nvarchar(255) = 'Seattle', 
@Zip nvarchar(10), 
@HomePhone nvarchar(255) = null,
@WorkPhone nvarchar(255)=null
As
--Declare variables
Declare @Passkey int =dbo.fx_Seed()
Declare @HomePhoneType int =1
Declare @WorkPhoneType int =2
if exists
 (Select PersonKey from Person
 Where PersonLastName =@PersonLastName
 And PersonFirstname=@PersonFirstName
 And PersonUserName=@PersonUsername)
Begin --begin if
Print 'Person already exists'
end --end if
Else
Begin--begin else
Begin Tran --start transaction
Begin try--start Try
--insert into Person
Insert into Person
(PersonLastName, PersonFirstName, PersonUsername, 
PersonPlainPassword, 
Personpasskey, PersonEntryDate, PersonUserPassword)
Values(@PersonLastName, @PersonFirstName,
@PersonUsername, @PersonPlainPassword,
@Passkey, GetDate(),dbo.fx_hashPass(@PassKey, @PersonPlainPassword))
--get person key
Declare @PersonKey int = Ident_Current('Person')
--insert int personAddress
Insert into PersonAddress(Street, Apartment, State, City, Zip, PersonKey)
Values (@Street, @Apartment,@State, @City,@Zip, @PersonKey)
--insert home phone
Insert into PersonContact(PersonKey, ContactInfo, ContactTypeKey)
Values(@PersonKey, @HomePhone, @HomePhoneType)
--insert work phone
Insert into PersonContact(PersonKey, ContactInfo, ContactTypeKey)
Values(@PersonKey, @WorkPhone, @WorkPhoneType)
commit tran --all is good write it
End try
Begin catch --if an error will fall here
Rollback Tran--undo the transaction
print 'Transaction rolled back'
print Error_Message();
End Catch
end --end else

exec Usp_AddPerson
@PersonLastName='Munse', 
@PersonFirstName='Nelson', 
@PersonUsername='NMunse@gmail.com', 
@PersonPlainPassword='MunsePass', 
@Street='1004 North Somewhere', 
 @Zip='98100', 
 @HomePhone='2065559773', 
 @WorkPhone='2065558729'