Alter procedure usp_LeaseInfo
@Last varchar(30)
AS
Select Lastname, 
	Firstname, 
	LeaseNumber, 
	AptNumber,
	RentAmount,
	StartDate
From Tenant t
Inner Join Lease l
on t.tenantID=l.TenantID
Where LastName=@Last

Select * from Tenant

exec usp_LeaseInfo @Last='Carrol'  

Create View vw_LeaseInfo
As 
Select Lastname, 
	Firstname, 
	LeaseNumber, 
	AptNumber,
	RentAmount,
	StartDate
From Tenant t
Inner Join Lease l
on t.tenantID=l.TenantID

Select * from vw_leaseInfo
Where Lastname='Carrol'

Alter proc usp_NewMagazine
@magName varchar(100),
@MagType char(10),
@SubscriptionTypeID int,
@Price money
AS
Begin tran
--Magazine insert
Begin try
If Not exists
	(Select MagName 
       from Magazine
		Where Magname=@magName)
Begin --begin if true
Insert into Magazine(MagName, MagType)
Values(@magName, @Magtype)
End
Else
Begin
Rollback tran
print 'magazine already exists'
Return
End --end if true
--get the identity
Declare @MagID int
Set @MagID=@@identity

--insert into Magazine
Insert into MagazineDetail(MagID, SubscriptTypeID, 
subscriptionPrice)
Values (@MagID, @SubscriptionTypeID, @Price)
Commit tran
End try
Begin Catch
	Rollback Tran
	print 'Insert Failed'
End Catch



Select * from Magazine

usp_newMagazine
@Magname='Scientific American',
@MagType='Monthly',
@SubscriptionTypeID=5,
@Price=125

Select * from magazineDetail