Select * from vw_SubscriptionSummary

Alter Proc usp_CustomerSubscription
@lastname varchar(30),
@firstName varchar(25)
As
Select CustLastName, CustFirstName, MagName, SubscriptionStart, SubscriptionEnd
From Customer c
inner join Subscription s
on c.CustID=s.CustID
inner join MagazineDetail md
on md.MagDetID=s.magDetID
inner join Magazine m
on m.Magid=md.MagID
Where CustLastName Like @lastname
And CustFirstName Like @firstName

exec usp_CustomerSubscription 
@lastname='%',
@firstname='Lenny'


Alter proc usp_AddMagazine
@magazineName varchar(100),
@magType char(10),
@SubscriptTypeID int,
@subscriptionPrice money
As
Declare @magid int
--test to see if the magazine exists
if exists
	(Select MagName
			from Magazine
				Where magname=@magazineName)
Begin --if it does
--get the magazine id
Select @magID=MagID 
From Magazine
Where MagName = @magazineName
--check to see if there is a subscription type
--for this magazine that matches the paramenter
--if not insert the new subscription type
	if  not exists
		(Select subscriptTypeID 
			from MagazineDetail
				Where subscriptTypeID=@SubscriptTypeID
				And MagID = @MagID)
      Begin --do the insert
			Insert into MagazineDetail(MagID, SubscriptTypeID,				SubscriptionPrice)
			Values (@magid, @SubscriptTypeID, @SubscriptionPrice)
			End --for second if
			Else -- if does exixt
            Begin
			print 'Magazine and subscription type already exist'
			Return-- cancel the procedure
			End -- end else
End --for first if
Else --for first if
Begin
Begin Tran
Begin try
Insert into magazine (MagName,MagType)
Values (@magazineName, @magType)

Set @magid = @@identity

Insert into MagazineDetail(MagID, SubscriptTypeID,SubscriptionPrice)
Values (@magid, @SubscriptTypeID,@SubscriptionPrice)
commit tran
end try

Begin catch
Rollback tran
print error_message()
end catch
end



alter table Magazine
Add Constraint ch_type Check (magtype in ('Monthly', 'Quarterly', 'Annually', 'Weekly', 'Daily'))

usp_AddMagazine
@magazineName='hair cut daily',
@magType='day',
@SubscriptTypeID=5,
@SubscriptionPrice=80.50

Select * from Magazine
Select * from MagazineDetail