Need Help Wit SQL Assignment

Just started SQL Class and I am struggling with an assignment.
Would someone be kind to give me some help please?

your task is to make a stored procedure that will only billcustomers making monthly payments higher than $500.A Customer can have multiple Plans (services for which they pay amonthly fee).

You have to execute a billing procedure for each single active planunder an eligible customer account. Eligible customer account is anaccount that makes estimated monthly payment higher than $500.

You are given 2 tables, "Customers" and "Plans". Table "Customers" has columns: CustomerID, EmailAddressTable "Plans" has columns: PlanID, CustomerID, MonthlyPrice, isClosed,NextBillDate

Also, you are given two stored procedures named "Bill_Plan" and"Send_Email". All stored procedures returns 0 upon success and negativenumber upon failure.The "Bill_Plan" actually bills a customer for a plan. The storedprocedure takes one input parameter and have one output parameter.

Bill_Plan
@nPlanID = @PlanID,
@dtNewBillDate = @NextBillDate output

You have to use the output parameter @NextBillDate to updatePlans.NextBillDate with its new value.

Upon successful billing you have to send en email to the customer'semail address. The "Send_Email" stored procedure takes two inputparameters.
Send_Email
@sEmailAddress = @EmailAddress,
@sSubject = 'Your Invoice is Ready'
You have to rollback any single billing transaction upon any kind offailure and continue billing the remaining plans. (Important!!!)

OK HERE IS WHAT I HAVE AND I AM STUCK!!

/**********************************************************************
* SP Name: sp_Bill_Cust_monthly_over_500
* Author: Michael Webb
* Date: 05/08/2008
* 
*
* Purpose:
* stored procedure that will only billcustomers making monthly payments higher than $500.
* A Customer can have multiple Plans (services for which they pay amonthly fee).
* You have to execute a billing procedure for each single active planunder an eligible customer account.
* Eligible customer account is anaccount that makes estimated monthly payment higher than $500.
* You are given 2 tables, "Customers" and "Plans". Table "Customers" has columns: CustomerID, 
* EmailAddressTable "Plans" has columns: PlanID, CustomerID, MonthlyPrice, isClosed,NextBillDate Also,
* you are given two stored procedures named "Bill_Plan" and"Send_Email". 
* All stored procedures returns 0 upon success and negativenumber upon failure.
* The "Bill_Plan" actually bills a customer for a plan. The storedprocedure takes one input parameter 
* and have one output parameter. Bill_Plan@nPlanID = @PlanID,@dtNewBillDate = @NextBillDate 
* output You have to use the output parameter @NextBillDate to updatePlans.NextBillDate with its
* new value. Upon successful billing you have to send en email to the customer'semail address.
* The "Send_Email" stored procedure takes two inputparameters. Send_Email@sEmailAddress = @EmailAddress,
* @sSubject = 'Your Invoice is Ready' You have to rollback any single billing transaction upon any 
* kind offailure and continue billing the remaining plans. (Important!!!)
*
**********************************************************************

*
*/

if exists( select * from sysobjects where name='sp_Bill_Cust_monthly_over_500' and type='P' )
drop proc sp_Bill_Cust_monthly_over_500
go

CREATE PROCEDURE sp_Bill_Cust_monthly_over_500
AS



/***************************
** Variable declarations **
***************************
*/

declare @tc int /* tran count */
declare @nPlanID int
declare @dtNewBillDate
declare @sEmailAddress varchar(100)
declare @sSubject varchar (100)
Set @sSubject = 'Your Invoice is Ready'

/*******************************************************************
** Check whether to start a new transaction, or save the old one
*******************************************************************
*/

select @tc = @@trancount
if ( @tc > 0 )
save tran my_tran
else
begin tran my_tran


/********************************************************************
** PLACE YOUR PROCEDURE CODE HERE **
********************************************************************
*/
Declare BillCur Cursor

For

Select P.PlanID, C.EmailAddress
From customer C
INNER JOIN plans P on c.customerID = p.customerID
Where P.MonthlyPrice > 500
and P.isclose = 0


Open BillCur
Fetch Next From Cursor Into @nPlanID, @sEmailAddress 
Bill_Plan( @nPlanID)


End

/********************************************************************
** Transaction was successful
** - Commit if this procedure issued a begin tran
********************************************************************
*/

if ( @tc = 0 )
commit tran my_tran

return 0

go

GRANT EXECUTE ON sp_Bill_Cust_monthly_over_500

This post has been edited by PsychoCoder: 21 May 2008 - 06:49 PM