Erland Sommarskog <es****@sommarskog.se> wrote in message news:<Xn**********************@127.0.0.1>...
Philip Mette (ph******@msn.com) writes: Does anyone have any good references they could recommend on Cursor
based SQL writing? I have to create SQL that can loop though records
simular to VB loops and I have been told that this is the way to go.
Any recommendations would be helpful.
Here is a quick example which says everything you need to know:
create procedure TSQL_ProductProc (@product int output)
as
begin
declare @sales int
declare c INSENSITIVE cursor for select intcolumn from tbl
open c
set @product = NULL
while 1 = 1
begin
fetch next from c into @sales
IF @@fetch_status <> 0
BREAK
set @product = coalesce(@product, 1) * @sales
end
close c
deallocate c
end
Notes:
1) Always make cursors INENSITIVE. The default keyset cursor is
incomprehensible, to me at least, and has given me more than one bad
negative performance surprise. (Might have happened more in SQL 6.5.)
2) By putting the FETCH statement first in the loop, the code does not
only get shorter, it is also easier to maintain. If you have two
FETCH statements, and one is at the end of a long loop, you can
easily forget to augment that statement if you add another column
to the cursor, giving difficult-to-find errors, since the cursors
works on the first iteration in the loop.
Incidently, this example is a case a cursor might be necessary, as
there is no way to do this sort of thing in a set-based statment.
(You can use SUM with LOG and EXP for a product, if you only have
positive values, but not in the general case.)
As others have pointed out, cursors should only be used as a last
resort. First try to find a set-based way of attacking the problem.
For a large data set, the difference in performance may several
magnitudes.
But using cursors can sometimes cut development time considerably.
Say that you have a stored procedure that performs the task you
want to do on a set of scalar values received as parameters. Writing
a cursor loop which calls that procedure is a simple task. Rewriting
the stored procedure to handle an input set, may be a major undertaking.
And if you know that you the data set you will loop over will be modest
in size, it may simply not be worth the cost.
---Ok I will send you the table and fields names along with the psuedo
logic of what i need done to get you started since you ask.:) I will
send the create table and data statements soon. Here is what I need.
A collector takes or makes calls. During this call he gets a PTP or
promise to pay. This activity is stored in tblDailyActivities and the
activity status is 'OP' or Open by default. In another table,
tblDailyPayments store payments that have come in through out the
month.
Here are the tables:
tblDailyActivities
Record_Key_Num
Acct_Num
Activity_Date (Date Promise to Pay Was Made)
Promise_Amt_1
Due_Date (Date Next Payment Is Due)
Promise_Status
Total_Dollars_Coll
tblDailyPayments
Record_Key_Num
Acct_Num
Pay_Amt
Activity_Date (Date Payment Was Made)
Here is the logic.(Keep in mind that their can be more than one
promise made on same acct by the same or different collector so the
logic is handling this, you will see.)
For Each [Acct_Num and Due_Date] combination
Look for payments from the tblDailyPayments Table with an
activity_date that are within tblDailyActivites.Activity_Date + 10
days.
If the sum of Payments>=90% of
tblDailyActivities.Promise_Amt_1 then
Update tblDailyActivities.Promise_Status =
'PK' and tblDailyActivites.Total_Dollars_Coll= 'Sum (of Payments)'
And all other same [Acct_Num and Due_Date]
combination's update tblDailyActivities.Promise_Status = 'IP'
Else If the sum of Payments<90% of
tblDailyActivities.Promise_Amt_1, then
If tblDailyActivites.Activity_Date +10 days > today's date, then
tblDailyActivities.Promise_Status= 'OP' and exit loop
Else If tblCacsDailyActivites.Activity_Date +10 days < today's
date, then
Update tblCacsDailyActivities.Promise_Status = 'PB'
And move to record 2 of same [Acct_Num and Due_Date] combination.
And repeat until end of loop.
HERES SOME DATA SAMPLE:
TBLDAILYACTIVITIES
RecKey Account ActivityDate DueDate UserId PromiseAMt PromiseDate
PromiseStatus TotalColl
688849 7339344 08/06/2004 08/30/2004 MPJ5 525.00 08/16/2004
OP <Null>
688850 7339344 08/07/2004 08/30/2004 MPJ5 525.00 09/15/2004
OP <Null>
688851 7339344 08/20/2004 09/30/2004 MPLI 600.00 09/15/2004
OP <Null>
688852 7339344 08/21/2004 09/30/2004 MPLT 700.00 10/15/2004
OP <Null>
688853 6116655 08/12/2004 08/30/2004 MP60 500.00 09/15/2004
OP <Null>
688854 6116655 08/12/2004 08/30/2004 MP61 550.00 09/15/2004
OP <Null>
TBLDAILY PAYMENTS
RecKey Account PayAmt ActivityDate
1254 7339344 225.00 08/10/2004
1255 7339344 295.00 08/11/2004
1259 7339344 300.00 08/20/2004
1354 6116655 250.00 08/13/2004
OUTPUT
RecKey Account ActivityDate DueDate UserId PromiseAMt PromiseDate
PromiseStatus TotalColl
688849 7339344 08/06/2004 08/30/2004 MPJ5 525.00 08/16/2004
PK 520.00
688850 7339344 08/06/2004 08/30/2004 MPJ5 525.00 09/15/2004
IP <Null>
688851 7339344 08/20/2004 09/30/2004 MPLI 525.00 09/15/2004
OP 300.00
688852 7339344 08/21/2004 09/30/2004 MPLT 525.00 10/15/2004
IP <Null>
688853 6116655 08/12/2004 08/30/2004 MP60 500.00 09/15/2004
PB 250.00
688854 6116655 08/12/2004 08/30/2004 MP61 550.00 09/15/2004
IP <Null>
THE FIRST TABLE REPRESENTS ACTIVITIES THE SECOUND PAYMENTS THAT COME
IN.
THE 3RD IS HOW THE DATA SHOULD LOOK. THIS IS BASED OFF OF ACCT AND
DUEDATE COMBINATIONS SO THE LOOP
WILL STARTS OVER ON THE SAME ACCT IF THE DUEDATE CHANGES. SINCE THE
2ND GROUP OF RECORDS STILL
IS IN THE 10 DAY WINDOW FROM ACTIVITY DATE TO TODAY WE KEEP IT OPEN
EVEN THOUGH THE AMOUNT IS LESS THAN 90%.
BECAUSE THEY HAVE A FEW MORE DAYS TO GET MORE PAYMENTS IN TO MAKE THIS
A PK. THE GOAL IS TO GET THE FIRST RECORD OF A
ACCT DUEDATE GROUP MARK IT EITHER PK OR PB AND MOVE TO THE NEXT UNTILL
YOU FIND A PK ONCE A PK IS FOUND MARK ALL OTHERS WITH
IN THAT GROUP IP. TOTAL DOLLARS IS ONLY RECORDED IN PK OP AND PB.
If this can be done without cursors I will be very thankful. You all
out on the posting boards have been great. Thanks so much for all your
assistance. You make SQL coding exciting.