Stored Procedures

--stored procedures--
--Add a new person  to person table
--add an address to personAddress
--add phone numbers to contact
--all tied together with personkey

--First version raw
Use Community_Assist
Go
Create proc usp_NewPerson
@PersonLastName nvarchar(255),
@PersonFirstName nvarchar(255), 
@PersonEmail nvarchar(255), 
@PassWord nvarchar(255), 
@PersonAddressApt nvarchar(255)=null, 
@PersonAddressStreet nvarchar(255), 
@PersonAddressCity nvarchar(255)='Seattle', 
@PersonAddressState nchar(2)='WA', 
@PersonAddressZip nchar(12), 
@HomePhone nvarchar(255)=null,
@WorkPhone nvarchar(255)=null
AS
--get seed and password
Declare @Seed int
Declare @hash varbinary(500)
Set @Seed=dbo.fx_GetSeed()
Set @hash=dbo.fx_HashPassword(@Seed, @PassWord)
--insert into Person
Insert into Person(
PersonLastName, 
PersonFirstName, 
PersonEmail, 
PersonPassWord, 
PersonEntryDate, 
PersonPassWordSeed
)
Values(
@PersonLastName,
@PersonFirstName,
@PersonEmail,
@Hash,
GetDate(),
@Seed
)
--get the personkey for the person just inserted
declare @key int
Set @key=Ident_current('Person')
--insert into personAddress
Insert into PersonAddress( 
PersonAddressApt, 
PersonAddressStreet, 
PersonAddressCity, 
PersonAddressState, 
PersonAddressZip, 
PersonKey)
Values(
@PersonAddressApt,
@PersonAddressStreet, 
@PersonAddressCity, 
@PersonAddressState, 
@PersonAddressZip, 
@Key)
--get the contact type keys
Declare @homekey int
Declare @WorkKey int
Select @homekey=ContactTypekey from ContactType Type 
where contactTypeName='Home Phone'
Select @Workkey=ContactTypekey from ContactType Type 
where contactTypeName='Work Phone'
--if the value is not null insert it
if @HomePhone is not null
Begin
    Insert into contact(ContactNumber, ContactTypeKey, PersonKey)
 Values(@homePhone, @homeKey, @key)
End
if @WorkPhone is not null
Begin
    Insert into contact(ContactNumber, ContactTypeKey, PersonKey)
 Values(@WorkPhone, @WorkKey, @key)
End
Go
--test first version
Exec usp_NewPerson
@PersonLastName='Miller',
@PersonFirstName='Steve', 
@PersonEmail='steve.miller@gmail.com', 
@PassWord='MillerPass',  
@PersonAddressStreet='Space Cowboy Ave',  
@PersonAddressZip='98001', 
@HomePhone='2065552109'

Select * from contact where personkey=131

Go
--add try catch and transactions
Alter proc usp_NewPerson
@PersonLastName nvarchar(255),
@PersonFirstName nvarchar(255), 
@PersonEmail nvarchar(255), 
@PassWord nvarchar(255), 
@PersonAddressApt nvarchar(255)=null, 
@PersonAddressStreet nvarchar(255), 
@PersonAddressCity nvarchar(255)='Seattle', 
@PersonAddressState nchar(2)='WA', 
@PersonAddressZip nchar(12), 
@HomePhone nvarchar(255)=null,
@WorkPhone nvarchar(255)=null
AS
--get seed and password
Declare @Seed int
Declare @hash varbinary(500)
Set @Seed=dbo.fx_GetSeed()
Set @hash=dbo.fx_HashPassword(@Seed, @PassWord)
Begin Tran--begin a transaction
Begin Try
--insert into Person
Insert into Person(
PersonLastName, 
PersonFirstName, 
PersonEmail, 
PersonPassWord, 
PersonEntryDate, 
PersonPassWordSeed
)
Values(
@PersonLastName,
@PersonFirstName,
@PersonEmail,
@Hash,
GetDate(),
@Seed
)
--get the personkey for the person just inserted
declare @key int
Set @key=Ident_current('Person')
--insert into personAddress
Insert into PersonAddress( 
PersonAddressApt, 
PersonAddressStreet, 
PersonAddressCity, 
PersonAddressState, 
PersonAddressZip, 
PersonKey)
Values(
@PersonAddressApt,
@PersonAddressStreet, 
@PersonAddressCity, 
@PersonAddressState, 
@PersonAddressZip, 
@Key)
--get the contact type keys
Declare @homekey int
Declare @WorkKey int
Select @homekey=ContactTypekey from ContactType Type 
where contactTypeName='Home Phone'
Select @Workkey=ContactTypekey from ContactType Type 
where contactTypeName='Work Phone'
--if the value is not null insert it
if @HomePhone is not null
Begin
    Insert into contact(ContactNumber, ContactTypeKey, PersonKey)
 Values(@homePhone, @homeKey, @key)
End
if @WorkPhone is not null
Begin
    Insert into contact(ContactNumber, ContactTypeKey, PersonKey)
 Values(@WorkPhone, @WorkKey, @key)
End
Commit tran
End Try
Begin Catch
Rollback tran
print error_message()
End catch
go
--second test this should throw error
Exec usp_NewPerson
@PersonLastName='Miller',
@PersonFirstName='Steve', 
@PersonEmail='steve.miller@gmail.com', 
@PassWord='MillerPass',  
@PersonAddressStreet='Space Cowboy Ave',  
@PersonAddressZip='98001', 
@HomePhone='2065552109'

Exec usp_NewPerson
@PersonLastName='Stewart',
@PersonFirstName='James', 
@PersonEmail='James.Stewart@gmail.com', 
@PassWord='StewartPass',  
@PersonAddressStreet='201 Borah Ave',  
@PersonAddressZip='98001', 
@HomePhone='2065552166'

Select * From Person

Go
Create procedure usp_UpdatePerson
@PersonLastName nvarchar(255),
@PersonFirstName nvarchar(255), 
@PersonEmail nvarchar(255),
@PersonKey int
As
Update Person
Set PersonLastName=@personLastname,
PersonFirstName=@personfirstname,
PersonEmail=@personEmail
Where personkey = @personkey
go

exec usp_UpdatePerson
@personlastname='Manning',
@personFirstname='Louis',
@personEmail='LManning@gmail.com',
@Personkey=3






Select * from Person