Stored Procedures

/*********************
procedure for adding a new person
-get all the fields for new person and address
-Check to make sure the person doesn't exist
-insert into person
     -- get their plain password
  --concatenate with a seed
  --hash it (use HashPassword Function)
--insert into PersonAddress
--Insert into Contact
--make sure all inserts happen
--if that fails roll it back
--if succeeds commit it
***********************************/
Create procedure usp_NewPerson
@PersonLastName nvarchar(255), 
@PersonFirstName Nvarchar(255), 
@PersonEmail nvarchar(255), 
@PlainPassWord nvarchar(50), 
@PersonAddressApt nvarchar(255)=null, 
@PersonAddressStreet nvarchar(255), 
@PersonAddressCity nvarchar(255)='Seattle', 
@PersonAddressState nvarchar(2)='Wa', 
@PersonAddressZip nvarchar(11),
@HomePhone nvarchar(255)=null,
@WorkPhone nvarchar(255)=null
As
Declare @hashed varbinary(500)
Declare @Seed int
Declare @PersonKey int
set @Seed = dbo.fx_GetSeed()
Set @hashed=dbo.fx_HashPassword(@seed, @PlainPassword)
--insert into Person
Insert into Person(PersonLastName, PersonFirstName, 
PersonEmail, PersonPassWord, PersonEntryDate, PersonPassWordSeed)
Values(@PersonLastName, @PersonFirstName, @PersonEmail, @hashed,
GetDate(), @seed)
--get the new PersonKey
set @personKey = IDENT_CURRENT('Person')
-- insert into personAddress
Insert into PersonAddress(PersonAddressApt, PersonAddressStreet, 
PersonAddressCity, PersonAddressState, PersonAddressZip, PersonKey)
Values(@PersonAddressApt, @PersonAddressStreet, @PersonAddressCity,
@PersonAddressState,@PersonAddressZip, @personKey)

Declare @home int =1
Declare @work int =2

If @HomePhone is not null
Begin
    Insert into Contact(ContactNumber, ContactTypeKey, PersonKey)
 Values(@HomePhone,@Home,@PersonKey)
end

If @WorkPhone is not null
Begin
    Insert into Contact(ContactNumber, ContactTypeKey, PersonKey)
 Values(@WorkPhone,@work,@PersonKey)
end
go

exec usp_NewPerson
@PersonLastName='Doe', 
@PersonFirstName='Jane', 
@PersonEmail='jane.doe@gmail.com', 
@PlainPassWord='DoePass', 
@PersonAddressStreet='1111 Martin Way', 
@PersonAddressZip='98000', 
@WorkPhone='2065551043'

Select * from PersonAddress
Select * from Contact

--Second version adding tran and try catch
go
Alter procedure usp_NewPerson
@PersonLastName nvarchar(255), 
@PersonFirstName Nvarchar(255), 
@PersonEmail nvarchar(255), 
@PlainPassWord nvarchar(50), 
@PersonAddressApt nvarchar(255)=null, 
@PersonAddressStreet nvarchar(255), 
@PersonAddressCity nvarchar(255)='Seattle', 
@PersonAddressState nvarchar(2)='Wa', 
@PersonAddressZip nvarchar(11),
@HomePhone nvarchar(255)=null,
@WorkPhone nvarchar(255)=null
As
Declare @hashed varbinary(500)
Declare @Seed int
Declare @PersonKey int
--begin the transaction
Begin Tran
--begin the try
Begin Try
set @Seed = dbo.fx_GetSeed()
Set @hashed=dbo.fx_HashPassword(@seed, @PlainPassword)
--insert into Person
Insert into Person(PersonLastName, PersonFirstName, 
PersonEmail, PersonPassWord, PersonEntryDate, PersonPassWordSeed)
Values(@PersonLastName, @PersonFirstName, @PersonEmail, @hashed,
GetDate(), @seed)
--get the new PersonKey
set @personKey = IDENT_CURRENT('Person')
-- insert into personAddress
Insert into PersonAddress(PersonAddressApt, PersonAddressStreet, 
PersonAddressCity, PersonAddressState, PersonAddressZip, PersonKey)
Values(@PersonAddressApt, @PersonAddressStreet, @PersonAddressCity,
@PersonAddressState,@PersonAddressZip, @personKey)

Declare @home int =1
Declare @work int =2

If @HomePhone is not null
Begin
    Insert into Contact(ContactNumber, ContactTypeKey, PersonKey)
 Values(@HomePhone,@Home,@PersonKey)
end

If @WorkPhone is not null
Begin
    Insert into Contact(ContactNumber, ContactTypeKey, PersonKey)
 Values(@WorkPhone,@work,@PersonKey)
end
Commit Tran--if all is good
End Try 
Begin Catch--if there is an error
rollback tran --rollback
print error_message()
end catch
go

exec usp_NewPerson
@PersonLastName='Doh', 
@PersonFirstName='Jennifer', 
@PersonEmail='j.doh@gmail.com', 
@PlainPassWord='DohPass', 
@PersonAddressStreet='1111 Martin Way', 
@PersonAddressZip='98000', 
@WorkPhone='2065551043'

Go
--check to see if person exists
Alter procedure usp_NewPerson
@PersonLastName nvarchar(255), 
@PersonFirstName Nvarchar(255), 
@PersonEmail nvarchar(255), 
@PlainPassWord nvarchar(50), 
@PersonAddressApt nvarchar(255)=null, 
@PersonAddressStreet nvarchar(255), 
@PersonAddressCity nvarchar(255)='Seattle', 
@PersonAddressState nvarchar(2)='Wa', 
@PersonAddressZip nvarchar(11),
@HomePhone nvarchar(255)=null,
@WorkPhone nvarchar(255)=null
As
--check to see if person exists
--emails are unique so should be sufficient
--otherwise check several of the fields
If not exists
   (Select personKey from Person 
     where PersonEmail = @PersonEmail)
Begin --write the new person
Declare @hashed varbinary(500)
Declare @Seed int
Declare @PersonKey int
--begin the transaction
Begin Tran
--begin the try
Begin Try
set @Seed = dbo.fx_GetSeed()
Set @hashed=dbo.fx_HashPassword(@seed, @PlainPassword)
--insert into Person
Insert into Person(PersonLastName, PersonFirstName, 
PersonEmail, PersonPassWord, PersonEntryDate, PersonPassWordSeed)
Values(@PersonLastName, @PersonFirstName, @PersonEmail, @hashed,
GetDate(), @seed)
--get the new PersonKey
set @personKey = IDENT_CURRENT('Person')
-- insert into personAddress
Insert into PersonAddress(PersonAddressApt, PersonAddressStreet, 
PersonAddressCity, PersonAddressState, PersonAddressZip, PersonKey)
Values(@PersonAddressApt, @PersonAddressStreet, @PersonAddressCity,
@PersonAddressState,@PersonAddressZip, @personKey)

Declare @home int =1
Declare @work int =2

If @HomePhone is not null
Begin
    Insert into Contact(ContactNumber, ContactTypeKey, PersonKey)
 Values(@HomePhone,@Home,@PersonKey)
end

If @WorkPhone is not null
Begin
    Insert into Contact(ContactNumber, ContactTypeKey, PersonKey)
 Values(@WorkPhone,@work,@PersonKey)
end
Commit Tran--if all is good
End Try 
Begin Catch--if there is an error
rollback tran --rollback
print error_message()
end catch
end--end of if
Else
Begin
   print 'Person already exists'
End
go

exec usp_NewPerson
@PersonLastName='Burton', 
@PersonFirstName='Richard', 
@PersonEmail='Richard.Burton@gmail.com', 
@PlainPassWord='BurtonPass', 
@PersonAddressStreet='1111 Mecca Way', 
@PersonAddressZip='98000', 
@WorkPhone='2065551333'
go
---Stored proc to update own info
Create proc usp_UpdatePersonInfo
@PersonKey int,
@PersonLastName nvarchar(255), 
@PersonFirstName Nvarchar(255), 
@PersonEmail nvarchar(255), 
@PersonAddressApt nvarchar(255)=null, 
@PersonAddressStreet nvarchar(255), 
@PersonAddressCity nvarchar(255), 
@PersonAddressState nvarchar(2), 
@PersonAddressZip nvarchar(11),
@HomePhone nvarchar(255)=null,
@WorkPhone nvarchar(255)=null
As
Begin tran
Begin try
Update Person
Set PersonLastName =@PersonLastName,
PersonFirstName=@PersonFirstName,
PersonEmail=@PersonEmail
where PersonKey = @personKey

Update PersonAddress
Set PersonAddressApt=@PersonAddressApt,
PersonAddressStreet=@PersonAddressStreet,
PersonAddressCity=@PersonAddressCity,
PersonAddressState=@personAddressState,
PersonAddressZip = @PersonAddressZip
Where PersonKey =@PersonKey

if @HomePhone is not null
Begin
  Update Contact
  Set ContactNumber=@HomePhone,
  ContactTypeKey=1
  where personKey =@PersonKey
end

if @WorkPhone is not null
Begin
  Update Contact
  Set ContactNumber=@WorkPhone,
  ContactTypeKey=2
  where personKey =@PersonKey
end
Commit Tran
End Try
Begin Catch
Rollback tran
Print Error_Message()
end catch
go
exec usp_UpdatePersonInfo
@PersonKey=134,
@PersonLastName='Burton', 
@PersonFirstName='Richard', 
@PersonEmail='JAnderson@gmail.com', 
@PersonAddressStreet='14994 Broadway', 
@PersonAddressCity='New York',
@PersonAddressState='NY',
@PersonAddressZip='13444', 
@WorkPhone='2065551666'
Go
select * from Person Where PersonEmail='Richard.Burton@gmail.com'
Select * from PersonAddress where personkey=134
Select * from Contact where personkey=134