SQL Class - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL Class

Author

Topic

mxwebb00
Starting Member

17 Posts

Posted - 2008-05-08 : 16:57:42

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!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-09 : 09:29:27

Can we see what you tried till now?

mxwebb00
Starting Member

17 Posts

Posted - 2008-05-09 : 14:22:03

Just getting started I hope to finish tonight.

/**********************************************************************
* 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 */

/*******************************************************************
** 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 **
********************************************************************
*/

/********************************************************************
** 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

I the most important thing the teacher try to teach us is if we don't how to do it go find the answer.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-05-09 : 15:46:39

I think you need to insert a query at this point:


/********************************************************************
** PLACE YOUR PROCEDURE CODE HERE **
********************************************************************
*/

CODO ERGO SUM

mxwebb00
Starting Member

17 Posts

Posted - 2008-05-09 : 18:09:53

I have a good Idea what I need to do. I just need some help on a few things like the correct syntax for cursor the fetch next record.
Also how to trap failure and assign it to @tc I think and do I have rollback correct?

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

Ok here is where I am struggling.
1. How to create a cursor
Then I call the Bill_Plan stored proc then the Send_Email
2. How to test the transaction for Failure
3. Make sure it roles back on Failure BUT CONTINUES TO Bill the remaining Plans!!

mxwebb00
Starting Member

17 Posts

Posted - 2008-05-11 : 12:11:03

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

mxwebb00
Starting Member

17 Posts

Posted - 2008-05-12 : 13:59:39

Come can someone tell me if I am close somthing is not right.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources