Stored Procedures 1

--stored procedures

Use MagazineSubscription
Go

Alter proc usp_GetSubscriptionSummary
@Month int =3
As
Select MONTH(SubscriptionStart) as [Month]
,COUNT (SubscriptionID) as [Subscriptions]
,SUM(SubscriptionPrice) as Total
From Subscription s
Inner Join MagazineDetail md
On md.MagDetID=s.MagDetID
Where MONTH(SubscriptionStart)=@month
Group by MONTH(SubscriptionStart)

go
exec usp_GetSubscriptionSummary 2

usp_GetSubscriptionSummary @Month=3


--stored procedure to enter a new magazine subscription
--new customer --add the customer
--will need the new customer ID
--Get the magazine
--what the subscription terms are for that magazine-magdetID
--insert the subscription information (date function)
--Both inserts should happen or none -- transaction and error trapping
Go
Alter proc usp_NewSubscription
--parameters provided by the user
@LastName varchar(30),
@FirstName varchar(25),
@CustAddress varchar(100),
@CustCity varchar(50),
@CustState Char(2),
@CustZipcode char(11),
@CustPhone char(10),
@magDetID int
As
Declare @StartDate datetime
set @StartDate = GETDATE()
Declare @EndDate Datetime
Begin Tran
Begin try
Insert into Customer (CustLastName,
CustFirstName,
CustAddress,
CustCity,
CustState,
CustZipcode,
CustPhone)
Values(
@LastName,
@FirstName,
@CustAddress,
@CustCity,
@CustState ,
@CustZipcode,
@CustPhone)

Declare @CustID int
Set @CustID=SCOPE_IDENTITY()

Declare @SubscriptionType int
Select @SubscriptionType=SubscriptTypeID
from MagazineDetail
Where MagDetID=@MagDetID


Insert into Subscription(
CustID,
MagDetID,
SubscriptionStart,
SubscriptionEnd)
Values(
@CustID,
@magDetID,
@StartDate,
dbo.func_SubscriptionEndDate(@subscriptionType, @StartDate))
commit tran
End try
Begin Catch
print error_message()
RollBack Tran
End Catch

exec usp_NewSubscription
@LastName ='schlitz',
@FirstName ='Malt',
@CustAddress='100 curbside',
@CustCity='Seattle' ,
@CustState='WA' ,
@CustZipcode='98000',
@CustPhone='2065551232' ,
@magDetID=100