How to Convert Cursor into While Loop In SqlServer

– 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
 
Reference: http://www.geekpedia.com/tutorial292_How-to-Convert-Cursor-into-While-Loop-In-SqlServer.html 


Written by