Hi All,
I have been asked to use a loop to replace the functionality of a cursor in sql.
This is the code I have come up with to replace the cursor using loops.
DECLARE
@Cyber_ExpectedFPD AS Datetime,
@MEsPassed AS Int,
@i AS Int,
@The_Date AS Datetime,
@start int,
@end int;
SELECT @start = ISNULL(MIN(ContractNo),0), @end = ISNULL(MAX(ContractNo),0) FROM Cybertrac_FirstHooks_E;
while @start <= @end
BEGIN
IF EXISTS(SELECT 1 FROM Cybertrac_FirstHooks_E WHERE Contract_No = @start)
BEGIN
SELECT @Cyber_ExpectedFPD = CONVERT(Datetime,LEFT(Cyber_ExpectedFPD,11),103)
FROM Cybertrac_FirstHooks_E
WHERE Contract_No = @start;
SELECT @MEsPassed=ISNULL(DATEDIFF(m,@Cyber_ExpectedFPD,Monthend_Proc_Date),0) FROM creditease.dbo.tblSetup_Variables;
IF @MEsPassed >3 SET @MEsPassed = 3;
SET @i = 1;
WHILE @i <= @MEsPassed
BEGIN
SET @The_Date = DATEADD(d, - 1, DATEADD(m, @i, DATEADD(d, - DAY(@Cyber_ExpectedFPD) + 1, @Cyber_ExpectedFPD)))
INSERT INTO Cybertrac_FirstHooks_H (Contract_No, Period, AsAtDate)
VALUES (@start,@i, @The_Date)
SET @i = @i + 1;
END
END
SET @start = @start + 1;
END
I was wondering if a loop or cursor is even necessary fro this. I have read something about using set operations instead, but do not know if it can be done with this.
Thanking you in advance.