Click here to Skip to main content
15,881,281 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
If i use to "while loop(also used cursor)" into my store procedure for updation of more than 100 rows with some condition then it is going to slow, it 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.
SQL
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 
Posted
Comments
AmitGajjar 23-Apr-13 8:57am    
can you post your actual cursor/while code. with pseudo code we are not able to understand what exactly you want to achieve. you can check if you can reduce IF conditions.
gvprabu 23-Apr-13 9:31am    
You can try with Derived Tables, give some more clear explaination , where u got Issue?
ZurdoDev 23-Apr-13 10:38am    
Post the relevant code.
RedDk 23-Apr-13 13:38pm    
Strikes me, given the pseudocode, that not using the obvious [Ind] index as the loop "jump" parameter means that the constraint of a cursor method is not the best way to go about looping. Use that [Ind] as the count of number of times to conditionally ""whatever" the content, incrementing, etc.
ExpertITM 23-Apr-13 14:13pm    
you can use sql server brocker service..

1 solution

When we deal with TSQL, it should be approached as set problem in contrast to row by row operations.

You can write update query for each combination of condition, which may update multiple rows in one go.

You need to work out interms of set instead of each row operation. Alternatively, you can do this in DataSet in programming layer(C#) and update in one go in SQL.
 
Share this answer
 

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