|
4>
5> create table Billings (
6> BankerID INTEGER,
7> BillingNumber INTEGER,
8> BillingDate datetime,
9> BillingTotal INTEGER,
10> TermsID INTEGER,
11> BillingDueDate datetime ,
12> PaymentTotal INTEGER,
13> CreditTotal INTEGER
14>
15> );
16> GO
1>
2> INSERT INTO Billings VALUES (1, 1, '2005-01-22', 165, 1,'2005-04-22',123,321);
3> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (2, 2, '2001-02-21', 165, 1,'2002-02-22',123,321.);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (3, 3, '2003-05-02', 165, 1,'2005-04-12',123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (4, 4, '1999-03-12', 165, 1,'2005-04-18',123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (5, 5, '2000-04-23', 165, 1,'2005-04-17',123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (6, 6, '2001-06-14', 165, 1,'2005-04-18',123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (7, 7, '2002-07-15', 165, 1,'2005-04-19',123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (8, 8, '2003-08-16', 165, 1,'2005-04-20',123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (9, 9, '2004-09-17', 165, 1,'2005-04-21',123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (0, 0, '2005-10-18', 165, 1,'2005-04-22',123,321);
2> GO
(1 rows affected)
1>
2> CREATE PROC spOpenBillings
3> AS
4> DECLARE Billings_Cursor CURSOR
5> GLOBAL SCROLL DYNAMIC
6> FOR
7> SELECT *
8> FROM Billings WHERE BillingTotal - CreditTotal - PaymentTotal > 0
9> OPEN Billings_Cursor
10> GO
1>
2> CREATE PROC spGetBilling
3> @BillingID int OUTPUT, @BankerID int OUTPUT,
4> @BillingNumber varchar(50) OUTPUT, @BillingDate smalldatetime OUTPUT,
5> @BillingTotal money OUTPUT, @PaymentTotal money OUTPUT,
6> @CreditTotal money OUTPUT, @TermsID int OUTPUT,
7> @BillingDueDate smalldatetime OUTPUT, @PaymentDate smalldatetime OUTPUT
8> AS
9> FETCH NEXT FROM Billings_Cursor INTO
10> @BillingID, @BankerID, @BillingNumber, @BillingDate, @BillingTotal,
11> @PaymentTotal, @CreditTotal, @TermsID, @BillingDueDate, @PaymentDate
12> RETURN @@FETCH_STATUS
13> GO
1>
2> CREATE PROC spCloseBillings
3> AS
4> CLOSE Billings_Cursor
5> DEALLOCATE Billings_Cursor
6>
7> drop PROC spOpenBillings;
8> drop PROC spGetBilling;
9> drop PROC spCloseBillings;
10>
11>
12> drop table Billings;
13> GO
1>
2>
|