Stored Procedure

First of all DON"T PANIC! stored procedures do have a bit of a learning curve. They are more like traditional programming. The first thing to do (always) is to be clear about what it is you are trying to do with the procedure. In our procedure we want to Add a new subscription to do this we must

• Get all the parameters (values) we need to insert into customer


• Get the Magazine name and the subscription type (is it for one year or 5 months etc.


• Insert the new customer


• Get the new customerId. It is an identity and we can use the built in variable
  @@Identity


• Use the magazine name to get the magID for the magazine


• Use the magid and the subscription type to get the MagDetID from the magazineDetail table


• use the Information we got to insert the Subscription. (I also used the func_EndDate to get the end date for the subscription

So here is the procedure: First Name the procedure and provide all the parameters you need. You can get them by looking at the columns each table needs for the inserts

Create Procedure usp_NewSubscription
@CustLastName varchar(30),
@CustFirstName varchar(25),
@CustAddress varchar(100),
@CustCity varchar(50),
@CustState char(2),
@CustZipcode char(11),
@CustPhone char(10),
@Magazine varchar(100),
@SubscriptTypeID int,
@StartDate DateTime
As
--the as keyword starts the body of the procedure
--The first thing we will do is insert the customer
Insert into Customer(CustLastName, CustFirstName, CustAddress, CustCity, CustState, CustZipcode, CustPhone)
Values(
@CustLastName,
@CustFirstName,
@CustAddress,
@CustCity,
@CustState,
@CustZipcode,
@CustPhone)
--the user has provided all these values as parameters
--now we get the new CustID created by the insert
Declare @CustID int
Set @CustID=@@Identity

--Next we look for the MagID using the magazine Name

Declare @MagID int
Select @MagID=Magid from Magazine where MagName=@Magazine

--with the magid and the subscription type (which was provided as a parameter
--we can look up the magdetid which is what we need for
--the insert into subscription

Declare @magDetID int
Select @magDetID=MagDetId
From MagazineDetail
where MagID=@MagID
And SubscriptTypeID=@SubscriptTypeID

--now we can do the acutal insert into subscription
--I use the function we created in class earlier
--for the end date

Insert into Subscription
(CustID, MagDetID, SubscriptionStart, SubscriptionEnd)
Values
(@CustID, @MagDetID, @StartDate,
dbo.func_SubscriptionEndDate(@MagDetID, @StartDate))

--this is the end of the procedure so far

So, as is, this isn't much of a procedure. We still need to couch it in a transaction
so that it either all happens or non of it happens. To do that we will need to add a try catch structure. We will also modify it to check if the customer already exists. If they do we will only write the subscription, if not we will write both the customer and the subscription, But as I said at the top of the post. DONT PANIC. We will work through it slowly, step by step.