SQL Server stored procedure avoid cursor

Question

I have the following SQL Server stored procedure :

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 <> -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

Output :

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

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.

Can anyone help me edit my stored procedure to avoid the cursor?

Thanks for response.

It's called a stored procedure because it's stored inside SQL Server - it's not a store procedure, doesn't have anything to do with a store. — marc_s
– marc_s, Commented Jun 18, 2012 at 5:20

user2737615 · Accepted Answer · 2013-09-02 08:55:56Z

4

I use this to avoid cursor in everywhere I need

DECLARE @num_rows     int
DECLARE @cnt          int
DECLARE @selected     int

DECLARE @table1 TABLE (Id int not null primary key identity(1,1), col1 int )  
INSERT into @table1 (col1) SELECT col1 FROM table2
SET @num_rows=@@ROWCOUNT

SET @cnt=0
WHILE @cnt<@num_rows
BEGIN
    SET @cnt=@cnt+1
    SELECT 
        @selected=col1
        FROM @table1
        WHERE Id=@cnt

    --do your stuff here--

END

answered Sep 2, 2013 at 8:55

user2737615

Sign up to request clarification or add additional context in comments.

Comments

SchmitzIT · Accepted Answer · 2012-11-01 10:06:41Z

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.

edited Nov 1, 2012 at 10:06

answered Jun 18, 2012 at 5:11

SchmitzIT

9,74912 gold badges71 silver badges96 bronze badges

Collectives™ on Stack Overflow

SQL Server stored procedure avoid cursor

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related