How to Convert Cursor into While Loop In SqlServer

How to Convert Cursor into While Loop In SqlServer
This article is about how we can change the cursor in the while loop in SQL Server. It's very useful in improving the performance of stored procedures.

I personally face this issues in many Store Procedures i.e. Cursor taking too much time to execute. I am sure many of you will be having similar problem.

I have worked on that issue and found following solution. It may not be perfect for cursor optimization but it does work in my case.

– Original Cursor Code —
Declare @fk_MedicationOrderId as Bigint
   Declare @AdminTime as Bigint
declare CUR_MEDICATIONORDERID cursor FOR
   select
   fk_MedicationOrderId
   from
   tbl_medicationorderDetail_trn
   where
   Actiondate > Getdate()
OPEN CUR_MEDICATIONORDERID
FETCH NEXT FROM CUR_MEDICATIONORDERID
   INTO @fk_MedicationOrderId
WHILE @@FETCH_STATUS =0
   BEGIN
Select @AdminTime=AdminTime from tbl_medicationorderDetail_trn
   where fk_medicationorderid = @fk_MedicationOrderId
Update tbl_TempMedicationOrderDetail_trn
   set ActionTime = @AdminTime
   set @AdminTime = ”
FETCH NEXT FROM CUR_MEDICATIONORDERID
   INTO @fk_MedicationOrderId
END
CLOSE CUR_MEDICATIONORDERID
   DEALLOCATE CUR_MEDICATIONORDERID
——————-
– Modified Cursor Code (faster) –
Declare @fk_MedicationOrderId as Bigint
   Declare @AdminTime as Bigint
Declare Table for the fields you need in the cursor.
declare @IDList table (ID Bigint)
Insert into @IDList
   select
   fk_MedicationOrderId
   from
   tbl_medicationorderDetail_trn
   where
   Actiondate > Getdate()
while (select count(ID) from @IDList) > 0
   begin
   select top 1 @fk_MedicationOrderId = ID from @IDList
Select @AdminTime=AdminTime from tbl_medicationorderDetail_trn
   where fk_medicationorderid = @fk_MedicationOrderId
Update tbl_TempMedicationOrderDetail_trn
   set ActionTime = @AdminTime
   set @AdminTime = ”
delete from @IDList where ID = @fk_MedicationOrderId
end

What I have done here is to convert “cursor” into “while loop”. It is just to get your data in any temporary table or table variable after that just delete the rows from that at end of loop.

It helps in improving performance, I have personally tried it in many stored procedures.

Let me know if you find any other useful way of improving performance.

Nathan Pakovskie is an esteemed senior developer and educator in the tech community, best known for his contributions to Geekpedia.com. With a passion for coding and a knack for simplifying complex tech concepts, Nathan has authored several popular tutorials on C# programming, ranging from basic operations to advanced coding techniques. His articles, often characterized by clarity and precision, serve as invaluable resources for both novice and experienced programmers. Beyond his technical expertise, Nathan is an advocate for continuous learning and enjoys exploring emerging technologies in AI and software development. When he’s not coding or writing, Nathan engages in mentoring upcoming developers, emphasizing the importance of both technical skills and creative problem-solving in the ever-evolving world of technology. Specialties: C# Programming, Technical Writing, Software Development, AI Technologies, Educational Outreach

Leave a Reply

Your email address will not be published. Required fields are marked *

Back To Top