0

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!!!)


Thank signed I don't want to flunk!

2
Contributors
1
Reply
2
Views
9 Years
Discussion Span
Last Post by ithelp
0

Customer has 1:n relation with Bill you already have enough information on how to create tables named Customer,Bill ,etc.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.