Click here to Skip to main content
15,881,600 members
Please Sign up or sign in to vote.
4.00/5 (1 vote)
See more:
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.
SQL
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.
Posted
Updated 25-Mar-14 19:55pm
v3
Comments
chaau 25-Mar-14 17:53pm    
Can't see a cursor in your code. Can you show us? Perhaps your manager has asked you to use cursors in place of loops?
Ruwaldo 26-Mar-14 1:41am    
This is not the cursor...this is the code I have made to replace the cursor

1 solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900