/*****************************
I found the error, we forgot to
insert our new lease number into the lease
table. I also moved the getdate() function
out of the insert into lease and assigned
it to a variable begindate.
This version should work
***********************************/

Alter procedure usp_NewLease
@FirstName varchar(20),
@LastName varchar(30),
@Phone char(10),
@AptNumber char(3),
@Increment char(2),
@Deposit money,
@Rent money
AS
Declare @TenantID char(10)
Begin transaction
Begin try

if exists
	(Select tenantID
		From Tenant
		Where FirstName=@Firstname
		And Lastname=@Lastname
		And phone =@Phone)
Begin --if the tenant does exist

Select @TenantID=TenantID
		From Tenant
		Where FirstName=@Firstname
		And Lastname=@Lastname
		And phone =@Phone

Update Lease
Set endDate = getdate()
Where TenantID=@TenantID
end

Else --if not tenant does not exist
Begin

Select @TenantID=dbo.func_TenantID(@firstname, @Lastname, @increment)

Insert into Tenant(TenantID, Lastname, Firstname, Phone)
Values(@TenantID, @LastName, @Firstname, @Phone)

end

Declare @LeaseNumber char(10)
Select @LeaseNumber=dbo.func_LeaseID(GetDate(),@aptNumber)

Declare @EndDate datetime
Select @EndDate=DateAdd("m",12,GetDate())


Declare @BeginDate datetime
Select @BeginDate=GetDate()
Insert into lease(LeaseNumber,StartDate,EndDate,DepositAmount,RentAmount,TenantID, AptNumber)
Values(@LeaseNumber,@BeginDate,@EndDate,@Deposit, @Rent,@TenantID, @aptNumber)

Commit Tran
end try
Begin Catch
print Error_Message()
Rollback Tran
End Catch


exec usp_NewLease
@FirstName = 'Bill',
@LastName ='Jones',
@Phone='2065554444',
@AptNumber= '201',
@Increment ='01',
@Deposit =1000,
@Rent = 1550