3
Answers

Alternative for SQL Cursor or While loop ?

chetan tembhre

chetan tembhre

11y
3.3k
1
If i use to "while loop or cursor" into my store procedure for updation of more than 100 rows with some condition then it is going to slow, its take more than 2 min for execution and sometime create "TimeOut" problem. Please suggest me alternative of Cursor/While Loop.
Please refer below scenario for same.

CREATE Table #Temp

(

  Ind INT IDENTITY(1,1),

  EmpID VARCHAR(50),

  DOJ DATE,

  SAL INT,

  Nationality VARCHAR(50)

)

DECLARE @MinIdt INT, @MaxIdt INT

INSERT INTO #Temp SELECT EMPID,DOJ,SAL FROM EMP --------- FOR SOME CONDITION

      DECLARE @MinIdt INT

    DECLARE @MaxIdt INT

SELECT @MinIdt = MIN(IDT), @MaxIdt = MAX(IDT) FROM  #Temp

      WHILE @MinIdt <= @MaxIdt

      BEGIN 

       -- Check 1st Condition

           --IF EXISTS

              --CHECK 2nd Condition

                     -- IF True 3rd Condition

                         --THEN                      

                            -- check Nationality for lets say Indian 4th Condition

                              --Update Emp table with condition

                               -- Update one more log table

                             --else  

                               --check for lets say NRI

                                           --Update Emp table with condition

                                           -- Update one more log table

                                  

    END

Answers (3)