Stored Procedures

use Community_Assist

--stored procedures
--script
--parameterized view
go
Create proc usp_CityProc
@City nvarchar(255)
As
Select PersonLastname [Last],
personFirstName [first],
PersonEmail Email,
PersonAddressCity City
From Person p
inner join PersonAddress pa
on p.PersonKey=pa.PersonKey
Where PersonAddressCity=@City

exec usp_CityProc 'Kent'

--more complicated procedure
--3 different stages (just the inserts, try catch error trapping
--check to see if already exits
--Register a new user
--insert into person
--when insert into person you need to hash the password
--insert into personAddress
--insert into contacts
go
Create proc usp_RegisterMark2
@lastName nvarchar(255),
@firstName nvarchar(255),
@Email nvarchar(255),
@Password nvarchar(255),
@Apartment nvarchar(255) =null,
@Street nvarchar(255),
@City nvarchar(255)= 'Seattle',
@State nchar(2) ='WA',
@Zip nchar(10),
@home nvarchar(255)= null,
@work nvarchar(255) =null
As
--get the random number seed
Declare @seed int = dbo.fx_GetSeed()
--Declare variable to store the hash
Declare @hashed varbinary(500)
--hash the plain text password
set @hashed = dbo.fx_HashPassword(@seed, @password)
--insert Person
Insert into Person(PersonLastName,
PersonFirstName, PersonEmail, PersonPassWord,
PersonEntryDate, PersonPassWordSeed)
Values(@lastName,@firstName,@email,
@hashed,GetDate(),@seed)
--get the most recent identity from Person
Declare @PersonKey int = Ident_Current('Person')
--insert into PersonAddress
Insert into PersonAddress (PersonAddressApt,
 PersonAddressStreet, PersonAddressCity, 
 PersonAddressState, PersonAddressZip, PersonKey)
 Values(@Apartment,@Street, @City,@state,@zip, @PersonKey)
 --insert into contact
 if @home is not null
 begin
    Insert into Contact(ContactNumber, ContactTypeKey, PersonKey)
 Values(@home, 1, @PersonKey)
 end
  if @work is not null
 begin
    Insert into Contact(ContactNumber, ContactTypeKey, PersonKey)
 Values(@Work, 2, @PersonKey)
 end
 go

 exec usp_RegisterMark2
@lastName='Branson', 
@firstName='Martin', 
@Email='bmartin@gmail.com', 
@Password='BransonPass', 
@Street='1001 North Elsewhere', 
@Zip='98100', 
@home='2065552314'

Select * from Contact

--second version with try catch
--transactions
go
Alter proc usp_RegisterMark2
@lastName nvarchar(255),
@firstName nvarchar(255),
@Email nvarchar(255),
@Password nvarchar(255),
@Apartment nvarchar(255) =null,
@Street nvarchar(255),
@City nvarchar(255)= 'Seattle',
@State nchar(2) ='WA',
@Zip nchar(10),
@home nvarchar(255)= null,
@work nvarchar(255) =null
As
--get the random number seed
Declare @seed int = dbo.fx_GetSeed()
--Declare variable to store the hash
Declare @hashed varbinary(500)
--hash the plain text password
set @hashed = dbo.fx_HashPassword(@seed, @password)
--begin transaction
begin tran
--begin try
Begin try
--insert Person
Insert into Person(PersonLastName,
PersonFirstName, PersonEmail, PersonPassWord,
PersonEntryDate, PersonPassWordSeed)
Values(@lastName,@firstName,@email,
@hashed,GetDate(),@seed)
--get the most recent identity from Person
Declare @PersonKey int = Ident_Current('Person')
--insert into PersonAddress
Insert into PersonAddress (PersonAddressApt,
 PersonAddressStreet, PersonAddressCity, 
 PersonAddressState, PersonAddressZip, PersonKey)
 Values(@Apartment,@Street, @City,@state,@zip, @PersonKey)
 --insert into contact
 if @home is not null
 begin
    Insert into Contact(ContactNumber, ContactTypeKey, PersonKey)
 Values(@home, 1, @PersonKey)
 end
  if @work is not null
 begin
    Insert into Contact(ContactNumber, ContactTypeKey, PersonKey)
 Values(@Work, 2, @PersonKey)
 end
 Commit tran --write the transaction
 End try --end the try
 Begin Catch --catch an error
 Rollback tran --undo anything that has been done
 print Error_Message()
 End catch
 go

exec usp_RegisterMark2
@lastName='Branson', 
@firstName='Martin', 
@Email='bmartin@gmail.com', 
@Password='BransonPass', 
@Street='1001 North Elsewhere', 
@Zip='98100', 
@home='2065552314'

--third and final version
--we will check to see if person exists
go
Alter proc usp_RegisterMark2
@lastName nvarchar(255),
@firstName nvarchar(255),
@Email nvarchar(255),
@Password nvarchar(255),
@Apartment nvarchar(255) =null,
@Street nvarchar(255),
@City nvarchar(255)= 'Seattle',
@State nchar(2) ='WA',
@Zip nchar(10),
@home nvarchar(255)= null,
@work nvarchar(255) =null
As
if Not exists
  (Select * from Person
    Where PersonEmail=@Email
 And PersonLastName = @LastName
 And PersonFirstName=@FirstName)
Begin --begin if
--get the random number seed
Declare @seed int = dbo.fx_GetSeed()
--Declare variable to store the hash
Declare @hashed varbinary(500)
--hash the plain text password
set @hashed = dbo.fx_HashPassword(@seed, @password)
--begin transaction
begin tran
--begin try
Begin try
--insert Person
Insert into Person(PersonLastName,
PersonFirstName, PersonEmail, PersonPassWord,
PersonEntryDate, PersonPassWordSeed)
Values(@lastName,@firstName,@email,
@hashed,GetDate(),@seed)
--get the most recent identity from Person
Declare @PersonKey int = Ident_Current('Person')
--insert into PersonAddress
Insert into PersonAddress (PersonAddressApt,
 PersonAddressStreet, PersonAddressCity, 
 PersonAddressState, PersonAddressZip, PersonKey)
 Values(@Apartment,@Street, @City,@state,@zip, @PersonKey)
 --insert into contact
 if @home is not null
 begin
    Insert into Contact(ContactNumber, ContactTypeKey, PersonKey)
 Values(@home, 1, @PersonKey)
 end
  if @work is not null
 begin
    Insert into Contact(ContactNumber, ContactTypeKey, PersonKey)
 Values(@Work, 2, @PersonKey)
 end
 Commit tran --write the transaction
 End try --end the try
 Begin Catch --catch an error
 Rollback tran --undo anything that has been done
 print Error_Message()
 End catch
 End--end if
 Else --if person does exist
 Begin
 print 'Already in database'
 End
 go

 exec usp_RegisterMark2
@lastName='Branson', 
@firstName='Martin', 
@Email='bmartin@gmail.com', 
@Password='BransonPass', 
@Street='1001 North Elsewhere', 
@Zip='98100', 
@home='2065552314'

--create a stored procedure to
--update address information
go
Create proc usp_UpdateAddress
@PersonAddressApt nvarchar(255), 
@PersonAddressStreet nvarchar(255), 
@PersonAddressCity nvarchar(255), 
@PersonAddressState nvarchar(255), 
@PersonAddressZip nvarchar(255), 
@PersonKey int
As
Begin tran
Begin try
Update PersonAddress
Set PersonAddressApt=@personAddressApt,
PersonAddressStreet=@personAddressStreet,
PersonAddressCity=@PersonAddressCity,
PersonAddressState = @PersonAddressState,
PersonAddressZip=@PersonAddressZip
Where PersonKey = @PersonKey
Commit tran
End Try
Begin Catch
Rollback tran
print Error_message()
End Catch

Select * from PersonAddress

Exec usp_UpdateAddress
@PersonAddressApt='10A',
@PersonAddressStreet='1001 North Mann Street', 
@PersonAddressCity='Seattle', 
@PersonAddressState='Wa', 
@PersonAddressZip='98001', 
@PersonKey=1