Stored procedures

--stored procedures
--parameterized view

use Community_Assist
go
Create proc usp_HRViewProc
@EmployeeKey int
As
Select PersonLastName,
PersonfirstName,
PersonEmail,
EmployeeHireDate,
EmployeeAnnualSalary
From Person p
inner Join Employee e
on p.PersonKey=e.PersonKey
Where EmployeeKey = @EmployeeKey

exec usp_HRViewProc @EmployeeKey=2
Go
--Create hash password function
Alter function fx_hashPassword2
(@seed int, @password nvarchar(50))
returns varbinary(500)
As
Begin
Declare @newPassword nvarchar(70)
set @newPassword = 
cast(@seed as nvarchar(20)) + @Password
Declare @hashed varbinary(500)
set @hashed = hashbytes('sha2_512', @newPassword)
return @hashed
End


Select * from businessrule
go
--new person stored procedure
Alter proc usp_NewPerson
@lastName nvarchar(255),
@FirstName nvarchar(255)=null,
@Email nvarchar(255),
@password nvarchar(50),
@AptNumber nvarchar(255)=null,
@Street nvarchar(255),
@City nvarchar(255)='Seattle',
@State nvarchar(255)='WA',
@Zip nvarchar(255),
@HomePhone nvarchar(255)
As
--test to see if person exists
if exists
 (Select PersonLastName, PersonEmail From Person
 Where PersonLastName=@LastName
 And PersonEmail=@Email)
 Begin--begin if
 Print 'Already registered'
 return
 End--end if

--create the password hash
Declare @seed int
Set @seed = dbo.fx_GetSeed()
declare @hashedPass varbinary(500)
set @hashedPass = dbo.fx_HashPassword(@seed,@password)

Begin tran --start a transaction
Begin try -- begin try
--insert into person
Insert into Person(  
PersonLastName, PersonFirstName,
 PersonEmail, PersonPassWord, 
 PersonEntryDate, PersonPassWordSeed)
 Values(@LastName, @FirstName,
 @Email, @hashedPass,GetDate(),@seed)

 --get the key of the person just inserted
 Declare @key int = ident_current('Person')
 --insert into PersonAddress
 Insert into PersonAddress(
 PersonAddressApt,
 PersonAddressStreet, 
 PersonAddressCity, PersonAddressState, 
 PersonAddressZip, PersonKey)
 Values( @AptNumber,@street,@city,@state,@Zip,@key)

 Insert into Contact([ContactNumber],
 [ContactTypeKey], [PersonKey])
 Values (@HomePhone, 1, @Key)

 Commit Tran --commit the transaction if no error
 End Try
 Begin Catch
 Rollback Tran
 Print Error_Message()
 End Catch


 exec usp_NewPerson
 @lastName = 'Conger', 
 @FirstName='Steve', 
 @Email='spconger@gmail.com', 
 @password='congerPass', 
 @Street='101 nowhere bld', 
 @Zip='98122', 
 @HomePhone='2065551201'

 Select * from PersonAddress
 Select * from Contact