SQL Server Stored Procedure Avoid Cursor

I have the following SQL Server stored procedure : BEGIN TRAN CREATE TABLE #TempTable ( SampleOrderID int, SampleOrderNo var

Solution 1:

I usually use something like the following:

SELECT @SampleOrderID = MIN (SampleOrderID) FROM #TempTable
WHILE @SampleOrderID IS NOT NULL
BEGIN

   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   

   SELECT @SampleOrderID = MIN (SampleOrderID) FROM #TempTable WHERE SampleOrderID > @SampleOrderID 

END

This code would replace the cursor stuff you have.