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 - 05:49 PM

Just for future reference, please you [code] tags.

Thanks and Good Luck!

What's your specific question?

I'm with Mike, you say you're stuck but give no explanation. Are you receiving any errors, if so what are they? What is this code doing versus what you want/need it to do? These are vital pieces of information needed to help us help you. You cannot just post code and expect us to figure out what you want or what's going wrong