Hi,

I have a table (tblContracts) which has 2 columns (Months, Value) where Months is the length of the contract and Value is the total value of the contract - typical values may be 12 months & £24000.
I want to insert the monthly payment schedule to another table (tblSchedule) where each row would represent 1 month's payment, so in my example there would be 12 rows written, each with a value of £2000. Also, there is a third column which needs to identify whether the payment is the first in a series ('N'ew) or part of an existing collection ('R'epeat).

So, what I need is a way of doing this (sp???) whereby data is read from tblContracts and written to tblSchedule. I guess that a Stored Procedure containing a loop and conditional statements is needed.

Can anyone offer any advice ????

Thanks
Gary

Well it is a more or less manual process for every contract, otherwise place it in a trigger to fire when a new contract has been added:

Create PROCEDURE updatecontracts
 @contractid  int         &nb sp;   --the contractid from tbl_contracts
AS
Declare @valcounter int      --Just a temp counter for the loop
DECLARE @series char(1)      --For new or recurring payment
DECLARE @noofmonth int       --how many month are in the schedule
DECLARE @value money         --how much is the contract
Set @valcounter = 1
Set @series = 'N'
Select @noofmonth = (Select [Month] from tblContracts where [id] = @contractid)
Select @value = (Select [Value] from tblContracts where [id] = @contractid)
WHILE @valcounter < @noofmonth + 1 
BEGIN   
 INSERT INTO tblSchedule (fk,paymentmonth, value, paymentseries)
 VALUES
  (@contractid, @valcounter, @value / @noofmonth, @series)
 Set @valcounter = @valcounter + 1
 Set @series = 'R' 
END
GO

Brilliant (almost )

Your solution works great Michael, excpet that the sp will only work for 1 contract at a time. What I need is for the sp to go through the entire contract table and insert the schedule details for each and every contract.

Please excuse my ignorance, but I have zero experience with sp's and dont have a clue here. In theory, it should be quite easy, but what about the payment type ('N' / 'R') - it would only be 'N' for the first contract, not the first payment of the other contracts.

Oh, ok here goes:
I was trying to get around re-inserting values into the schedule table that were already there using a coursor but first off I do not like cursors and for some reason it always resulted into an endless loop, anyways, here is a solution that should work even though it might not be the most elegant.

ALTER  PROCEDURE updatecontracts
AS
DECLARE @contractid int
Declare @valcounter int
DECLARE @series char(1)
DECLARE @noofmonth int
DECLARE @value money
Set @contractid = (Select TOP 1 c.[id] from tblcontracts c LEFT JOIN tblschedule s ON s.fk=c.[id])  
Set @noofmonth = (Select [Month] from tblContracts where [id] = @contractid)
Set @valcounter = 1
Set @series = 'N' 
WHILE (select count(*) from tblschedule s) < (Select SUM([MONTH]) from tblcontracts)
 BEGIN
 --This is the loop for inserting schedule
 WHILE @valcounter < @noofmonth + 1 
  BEGIN
   Set @value = (Select [Value] from tblContracts where [id] = @contractid)  
   INSERT INTO tblSchedule (fk,paymentmonth, value, paymentseries)
      VALUES
       (@contractid, @valcounter, @value / @noofmonth, @series)
    --Re-initializing variables after loop run
   Set @valcounter = @valcounter + 1
    Set @series = 'R' 
   END
 Set @contractid = (Select TOP 1 c.[id] from tblcontracts c LEFT OUTER JOIN tblschedule s ON s.fk=c.[id] WHERE s.fk IS NULL)  
  Set @noofmonth = (Select [Month] from tblContracts where [id] = @contractid)
  Set @valcounter = 1
 Set @series = 'N'
 --End inserting schedule loop
 END

Added: Change the script to Create Procedure instead of  Alter Procedure if you deleted it already.

Michael,

You are a  - that worked a treat and I am eternally grateful to you for your assistance .

Just a little background - My colleague is working on this project and coded the SQL to be executed directly from an asp page. Boy was it ugly !!!

Cursors are actually a nice feature of SQL, once you learn how to use them. This is probally the easiest way to use them, and it has worked every time for me. Assume that @col1 and @col2 are pre-defined variables. You also have to make sure that however many objects your select statement returns is how many objects you catch in the fetch next statement, even if you dont use them.

Begin Tran
Declare cur Cursor For Select column1, column2 From table
Open cur
Fetch Next From cur Into @col1, @col2
While @@Fetch_Status = 0
    Begin
    ...
    If @@ERROR <> 0
        Goto FAIL
    Fetch Next From cur Into @col1, @col2
    End
Close cur
Deallocate cur
Commit Tran
Goto DONE

FAIL:
Rollback Tran
Select -1 As RetVal,'SQL Error' As RetString

DONE:
--done

I'm sure they're useful but its been drummed into me to always try and find a 'set-based' method of doing what you want first.

In most cases you can, as long as you think in 'sets', however I have come across cases where its just been too difficult.

On another note, I've been doing the 'MS Programming SQL 2000' course this week, and its more admin and query optimisation than 'programming'.

So if anyones after a pure 'programming' course, don't do this one.

Cheers,
Andrew

Yeah, I ussually try to avoid using cursors, but there are some cases where you just can't avoid using them, exspecailly when you more or less write SQL code all day just about for your job. I've always viewed cursors as "evil" and am ussually able to find a way to not use them, but there are those times...

That course sounds like fun, wish I had time to do something like that.