Stored Procedures 2

Use MagazineSubscription
Go
/******************
handle a new subscription to a magazine
enter customer info
get what magazine they want
What terms for the subscription
Subscription
*Insert into customer
*Look up magazine detail
*Look up subscription type
*Insert into subscription
*************************/
Alter proc usp_NewMagazineSubscription
--parameters provided by the user
@LastName varchar(30),
@FirstName varchar(25),
@Address varchar(100),
@City varchar(50),
@State char(2),
@Zip char(11),
@Phone char(10),
@magazine varchar(100),
@SubscriptTypeID int
AS
--internal variable
Declare @startDate DateTime
Set @startDate=GETDATE()
Declare @CustID int
Begin tran --begin transaction
Begin Try --begin error catching try
--test to see the customer already exists
if Exists
(Select CustID
From Customer
Where CustLastName= @LastName
And CustFirstName=@FirstName
And CustAddress=@Address
And CustCity=@City)
Begin
--if the customer does exist
--just get the customerID
Select @CustID=CustID
From Customer
Where CustLastName= @LastName
And CustFirstName=@FirstName
And CustAddress=@Address
And CustCity=@City
End
Else
Begin
--if the customer doesn't exist
--insert the new customer
Insert into Customer(
CustLastName,
CustFirstName,
CustAddress,
CustCity,
CustState,
CustZipcode,
CustPhone)
Values(
@LastName,
@FirstName,
@Address,
@City,
@State,
@Zip,
@Phone)

--get the current identity for the customer table
Set @CustID = IDENT_CURRENT('Customer')
End
--declare variables to store the
--magazine id and magazine detail id
Declare @MagID int
Declare @MagDetailID int

--get the magID
Select @MagID = MagID
From Magazine
Where MagName = @magazine
--get the magazine detailID
Select @MagDetailID=MagDetId
From MagazineDetail
Where MagID = @MagID
and SubscriptTypeID=@SubscriptTypeID
--Insert the subscription
--this insert uses the subscriptionEndDate
--function we created earlier
Insert into Subscription(CustID,
MagDetID,
SubscriptionStart,
SubscriptionEnd)
Values(@CustID,
@MagDetailID,
@startDate,
dbo.func_SubscriptionEndDate(@SubscriptTypeID, @startDate))

--if there have been no errors so far
--commit the transaction
commit tran
End try --end the try block

Begin Catch --begin the catch block
--if there are errors
--rollback the transaction
Rollback tran
--print only works in the query editor
print 'The subscription was unsuccessful'
print error_message()
End Catch --end catch end procedure

--try the procedure,
--vary the values to see what happens
--with new customers
--existing customers
--and different magazines and
--subscriptionType values
exec dbo.usp_NewMagazineSubscription
@LastName ='Yellow',
@FirstName ='Joe',
@Address ='100 Elsewhere',
@City= 'Las Vegas',
@State ='Nevada',
@Zip='90000',
@Phone ='4065551234',
@magazine ='XBox Anonymous',
@SubscriptTypeID= 2

--look at the results
Select * from Magazine
Select * from MagazineDetail
Select * From Customer
Select * From Subscription