title: SQL Server stored procedure avoid cursor
body: <p>I have the following SQL Server stored procedure :</p>
  
  <pre><code>BEGIN TRAN
  CREATE TABLE #TempTable (
                              SampleOrderID int,
                              SampleOrderNo varchar(512),
                              ChallanNoAndChallanDate varchar(MAX)
                          )
  CREATE NONCLUSTERED INDEX #IX_Temp2_1 ON #TempTable(SampleOrderID)
  
  DECLARE 
  @SQL as varchar(MAX)
  SET @SQL='      SELECT  SampleOrderID,  SampleOrderNo FROM SampleOrder WHERE SampleOrderID IN (37808,37805,37767,37571,37745,37772,37843,37394,37909,37905,37903) '
  INSERT INTO #TempTable  (SampleOrderID, SampleOrderNo)
  EXEC (@SQL)
  
  DECLARE 
  @SampleOrderID as int,
  @ChallanNoAndChallanDate as varchar(max)
  
  DECLARE Cur_AB1 CURSOR GLOBAL FORWARD_ONLY KEYSET FOR           
  SELECT  SampleOrderID FROM #TempTable
  OPEN Cur_AB1
  FETCH NEXT FROM Cur_AB1 INTO @SampleOrderID
  WHILE(@@Fetch_Status &lt;&gt; -1)
      BEGIN--2
          SET @ChallanNoAndChallanDate=''
          SELECT @ChallanNoAndChallanDate= COALESCE(@ChallanNoAndChallanDate+ ',', '') + CONVERT(VARCHAR(12),ChallanDate,106)+':'+ChallanNo  FROM Challan WHERE OrderID =@SampleOrderID AND OrderType=2
  
          UPDATE #TempTable SET ChallanNoAndChallanDate=@ChallanNoAndChallanDate WHERE SampleOrderID=@SampleOrderID   
          FETCH NEXT FROM Cur_AB1 INTO @SampleOrderID
      END--2
  CLOSE Cur_AB1
  DEALLOCATE Cur_AB1
  
  SELECT * FROM #TempTable
  DROP TABLE #TempTable
  COMMIT TRAN 
  </code></pre>
  
  <p>Output :</p>
  
  <pre><code>SamID       SamNo    ChallanNoAndDaet
  37394   37394   ,31 May 2012:151592
  37571   37571   ,31 May 2012:151580
  37745   37745   ,31 May 2012:151582
  37767   37767   ,30 May 2012:151507,31 May 2012:151576
  37772   37772   ,31 May 2012:151587
  37805   37805   ,31 May 2012:151574
  37808   37808   ,31 May 2012:151573
  37843   37843   ,31 May 2012:151588
  37903   37903   ,31 May 2012:151597
  37905   37905   ,31 May 2012:151596
  37909   37909   ,31 May 2012:151593
  </code></pre>
  
  <p>It works successfully for small volume of data but When i try to execute it on a Large volume (i.e. more then 500,000 record) my C# interface throws the time out exception.</p>
  
  <p>Can anyone help me edit my stored procedure to avoid the cursor? </p>
  
  <p>Thanks for response. </p>