none
Remove the cursor

    Question

  • hi, I have the script from which I have  to remove the cursor and implement the code in some another way rather than using cursor.


    DECLARE @AsOnDate SMALLDATETIME
    DECLARE @FlagProduction AS BIT

    DECLARE @tblAcAging AS TABLE
    (
    fLocation VARCHAR(3),
    fCategory VARCHAR(15),
    fAccount INT,
    fName VARCHAR(30),
    fOffice VARCHAR(2),
    fPhone VARCHAR(14),
    fType TINYINT, -- 1 - Private, 2 - Contract , 3 - Insurance, 0 - Unapplied Payments, 4 - Production
    fOver180 MONEY,
    fOver120 MONEY,
    fOver90 MONEY,
    fOver60 MONEY,
    fOver30 MONEY,
    fCurrent MONEY,
    fBalance MONEY,
    fLastPay MONEY,
    fLastDate SMALLDATETIME,
    fCheck VARCHAR(10),
    fTotal MONEY,
    fFlag VARCHAR(1),  --*  means sent for collection
    fCredit MONEY
    )


    SET @FlagProduction = 1
    SET @AsOnDate = CONVERT(SMALLDATETIME,'2012/NOV/30')

    IF @FlagProduction = 1
    BEGIN

    DECLARE @mFlocation VARCHAR(3)
    DECLARE @mFaccount INT
    DECLARE @mFPatient INT
    DECLARE @mFcategory VARCHAR(25)
    DECLARE @mFtxnumber INT
    DECLARE @mFtxsuffix TINYINT
    DECLARE @mFtxdate SMALLDATETIME
    DECLARE @mForthogen TINYINT
    DECLARE @mfDelete TINYINT 
    DECLARE @mePvt MONEY
    DECLARE @meIns MONEY
    DECLARE @mePayPvt MONEY
    DECLARE @mePvtCR MONEY
    DECLARE @mePvtDB MONEY
    DECLARE @mePayIns MONEY
    DECLARE @meInsCR MONEY
    DECLARE @meInsDB MONEY
    DECLARE @meOff VARCHAR(1)
    DECLARE @meLast VARCHAR(25)
    DECLARE @meFirst VARCHAR(25)
    DECLARE @mePhone VARCHAR(25)
    DECLARE @meColl  BIT


    DECLARE TempCursor CURSOR FOR
    SELECT 
    Account.Flocation,  
    Account.Faccount,  
    TxPayPlan.FPatient,  
    TxPayPlan.Fcategory,  
    TxPayPlan.Ftxnumber,  
    TxPayPlan.Ftxsuffix,  
    TxPayPlan.Ftxdate,  
    Txpayplan.Forthogen,   
    TxPayPlan.fDelete,  
    ePvt = Case When (TxPayPlan.fContractNo = 0) Then Fpvtdue Else 0  End,  
    eIns = TxPayPlan.Finsdue,  
    ePayPvt = Sum( Case When ((Payment.fPayType <=50)  
    AND (Payment.finsurance <> 1)  
    AND (PayDtl.fContractNo = 0)  
    AND (Txpayplan.Finscode = PayDtl.FinsKode)  
    AND (TxPayPlan.FPatient = PayDtl.FPatient)  
    AND (PayDtl.FDelete <> 1)  And Payment.fDelete <> 1   
    --AND (@rOrthoGen  IN (Payment.fOrthoGen,10))    
    AND (Payment.fpaydate <= @AsOnDate )) 
    Then 
    PayDtl.fAmount 
    Else 

    End),  
    ePvtCR = Sum( Case When ((Payment.fpaytype > 50)   
    AND (Payment.fpaytype <= 60)   
    AND (Payment.finsurance <> 1)   
    AND (PayDtl.fContractNo = 0)   
    AND (Txpayplan.Finscode = PayDtl.FinsKode)   
    AND (TxPAyPlan.FPatient = PayDtl.FPatient)   
    AND (PayDtl.FDelete <> 1)  And Payment.fDelete <> 1  
    --AND (@rOrthoGen  IN (Payment.fOrthoGen,10))    
    AND (Payment.fpaydate <= @AsOnDate )) 
    Then 
    PayDtl.fAmount 
    Else 

    End),  
    ePvtDB = Sum( Case When ((Payment.fpaytype > 60)  
    AND (Payment.finsurance <>1)  
    AND (PayDtl.fContractNo = 0)  
    AND (Txpayplan.Finscode = PayDtl.FinsKode)  
    AND (TxPAyPlan.FPatient = PayDtl.FPatient)  
    AND (PayDtl.FDelete <> 1)  And Payment.fDelete <> 1  
    --AND (@rOrthoGen  IN (Payment.fOrthoGen,10))    
    AND (Payment.fpaydate <= @AsOnDate )) 
    Then 
    PayDtl.fAmount 
    Else 

    End),  
    ePayIns = Sum( Case When ((payment.fpaytype <= 50)  
    AND (Payment.finsurance = 1)   
    AND (Txpayplan.Finscode = PayDtl.FinsKode)   
    AND (TxPAyPlan.FPatient = PayDtl.FPatient)  
    AND (PayDtl.FDelete <> 1)  And Payment.fDelete <> 1  
    --AND (@rOrthoGen  IN (Payment.fOrthoGen,10))    
    AND (Payment.fpaydate <= @AsOnDate )) 
    Then 
    PayDtl.fAmount 
    Else 

    End),  
    eInsCR = Sum( Case When ((payment.fpaytype > 50)   
    AND (Payment.fpaytype <= 60)  
    AND (Payment.finsurance = 1)  
    AND (Txpayplan.Finscode = PayDtl.FinsKode)  
    AND (TxPAyPlan.FPatient = PayDtl.FPatient)   
    AND (PayDtl.FDelete <> 1)  And Payment.fDelete <> 1  
    --AND (@rOrthoGen  IN (Payment.fOrthoGen,10))    
    AND (Payment.fpaydate <= @AsOnDate )) 
    Then 
    PayDtl.fAmount 
    Else 

    End),  
    eInsDB = Sum( Case When ((Payment.fpaytype > 60)  
    AND (Payment.finsurance = 1)   
    AND (Txpayplan.Finscode = PayDtl.FinsKode)   
    AND (TxPAyPlan.FPatient = PayDtl.FPatient)   
    AND (PayDtl.FDelete <> 1)  And Payment.fDelete <> 1  
    --AND (@rOrthoGen  IN (Payment.fOrthoGen,10))    
    AND (Payment.fpaydate <= @AsOnDate )) 
    Then 
    PayDtl.fAmount 
    Else 

    End),  
    eOff = TxPayPlan.Foffice,  
    eLast = Account.Flastname,  
    eFirst = Account.Ffirstname,  
    ePhone = Account.Fhomephone,  
    eColl = Account.fCollSend
    FROM 
    (
    (Account 
    INNER JOIN TxPayPlan 
    ON (Account.Flocation = TxPayPlan.Flocation) AND   
    (Account.Faccount = TxPayPlan.Faccount)
    )   
    LEFT JOIN PayDtl 
    ON (TxPayPlan.Flocation = PayDtl.fLocation) AND   
    (TxPayPlan.Faccount = PayDtl.fAccount) AND   
    (TxPayPlan.Ftxnumber = PayDtl.fTxNumber)
    )   
    LEFT JOIN Payment 
    ON (PayDtl.fLocation = Payment.fLocation) AND   
    (PayDtl.fPayBatchNo = Payment.fPayBatchNo)   
    WHERE 
    --TxPayPlan.flocation = @rLocation  AND
    --@rOffice IN (TxPayPlan.foffice,'')  
    --AND @rCategory IN (TxPayPlan.fcategory,'')  
    --AND @rOrthoGen  IN (TxPayPlan.fOrthoGen,10)  
    --AND 
    TxPayPlan.fDelete <> 1   
    GROUP BY 
    Account.Flocation,  
    Account.Faccount,  
    TxPayPlan.FPatient,   
    TxPayPlan.Fcategory,   
    TxPayPlan.Ftxnumber,   
    TxPayPlan.Ftxsuffix,   
    TxPayPlan.Ftxdate,   
    TxPayPlan.Forthogen,   
    TxPayPlan.fDelete,  
    TxPayPlan.fContractNo,  
    TxPayPlan.fInsdue,  
    TxPayPlan.fPvtDue,   
    TxPayPlan.fOffice,   
    Account.fLastName,   
    Account.fFirstName,   
    Account.fHomePhone,  
    Account.fCollSend  
    ORDER BY 
    Account.Faccount;  

    OPEN TempCursor 
    FETCH NEXT FROM TempCursor INTO @mFlocation,@mFaccount,@mFPatient,@mFcategory,@mFtxnumber,@mFtxsuffix,@mFtxdate,@mForthogen,@mfDelete,@mePvt,@meIns,@mePayPvt,@mePvtCR,@mePvtDB,@mePayIns,@meInsCR,@meInsDB,@meOff,@meLast,@meFirst,@mePhone,@meColl ;
    WHILE @@FETCH_STATUS = 0
    BEGIN
    DECLARE @PrivateAmount AS MONEY
    DECLARE @Insuranceamount AS MONEY
    DECLARE @Days INT
    SET @PrivateAmount = (ISNULL(@mePvt,0) + ISNULL(@mepvtdb,0)) - (ISNULL(@mepaypvt,0) + ISNULL(@mepvtcr,0))
    SET @Insuranceamount = (ISNULL(@meins,0) + ISNULL(@meinsDB,0)) - (ISNULL(@mepayins,0) + ISNULL(@meinsCR,0))
    IF @PrivateAmount = 0 AND @Insuranceamount = 0 GOTO SkipTxPayPlan
    SET @Days = DATEDIFF(d,@mFtxdate, @AsOnDate) +1 

    IF @Days < 0 GOTO SkipTxPayPlan

    IF @PrivateAmount = 0 GOTO NextInsurance

    IF NOT EXISTS( SELECT * FROM @tblAcAging WHERE fLocation = @mFlocation AND fAccount = @mFaccount AND fCategory = @mFcategory AND fType = 1 )
    BEGIN
    INSERT INTO @tblAcAging ( fLocation,fAccount,fCategory,fType,  fName, fOffice, fPhone , fOver180, fOver120,fOver90, fOver60, fOver30,fCurrent,fFlag ) 
    VALUES (@mFlocation,@mFaccount, @mFcategory, 1, ISNULL(@meLast,'')+' '+ISNULL(@meFirst,''),ISNULL(@meOff,''),ISNULL(@mePhone,''),0,0,0,0,0,0,CASE WHEN @meColl = 1 THEN '*' ELSE '' END    )
    END

    DECLARE @_Over180 AS MONEY 
    DECLARE @_Over120 AS MONEY 
    DECLARE @_Over90 AS MONEY 
    DECLARE @_Over60 AS MONEY 
    DECLARE @_Over30 AS MONEY 
    DECLARE @_Current AS MONEY

    SET @_Over180 = 0
    SET @_Over120  = 0
    SET @_Over90  = 0
    SET @_Over60  = 0
    SET @_Over30  = 0
    SET @_Current = 0

    IF @Days >= 180 SET @_Over180 = @PrivateAmount 
    IF @Days >= 120 AND @Days < 180 SET @_Over120  = @PrivateAmount 
    IF @Days >= 90 and @Days < 120 SET @_Over90  = @PrivateAmount 
    IF @Days >= 60 AND @Days < 90 SET @_Over60  = @PrivateAmount 
    IF @Days >= 30 AND @Days < 60 SET @_Over30  = @PrivateAmount 
    IF @Days >= 0 AND @Days < 30 SET @_Current = @PrivateAmount 

    UPDATE @tblAcAging 
    SET fOver180 = fOver180+ @_Over180 ,
    fOver120 = fOver120 + @_Over120 ,
    fOver90 = fOver90 + @_Over90 , 
    fOver60 = fOver60 + @_Over60 , 
    fOver30 = fOver30 + @_Over30 ,
    fCurrent = fCurrent + @_Current,
    fBalance = fCurrent + @_Current + fOver30 + @_Over30 + fOver60 + @_Over60 + fOver90 + @_Over90 + fOver120 + @_Over120 + fOver180+ @_Over180  
    WHERE fLocation = @mFlocation AND fAccount = @mFaccount AND fCategory = @mFcategory AND fType = 1;

    NextInsurance:
    IF @Insuranceamount = 0 GOTO SkipTxPayPlan

    IF NOT EXISTS( SELECT * FROM @tblAcAging WHERE fLocation = @mFlocation AND fAccount = @mFaccount AND fCategory = @mFcategory AND fType = 3 )
    BEGIN
    INSERT INTO @tblAcAging ( fLocation,fAccount,fCategory,fType,  fName, fOffice, fPhone , fOver180, fOver120,fOver90, fOver60, fOver30,fCurrent,fFlag ) 
    VALUES (@mFlocation,@mFaccount, @mFcategory, 3, ISNULL(@meLast,'')+' '+ISNULL(@meFirst,''),ISNULL(@meOff,''),ISNULL(@mePhone,''),0,0,0,0,0,0,CASE WHEN @meColl = 1 THEN '*' ELSE '' END    )
    END

    SET @_Over180 = 0
    SET @_Over120  = 0
    SET @_Over90  = 0
    SET @_Over60  = 0
    SET @_Over30  = 0
    SET @_Current = 0

    IF @Days >= 180 SET @_Over180 = @Insuranceamount
    IF @Days >= 120 and @Days < 180 SET @_Over120  = @Insuranceamount
    IF @Days >= 90 and @Days < 120 SET @_Over90  = @Insuranceamount
    IF @Days >= 60 AND @Days < 90 SET @_Over60  = @Insuranceamount
    IF @Days >= 30 and @days < 60 SET @_Over30  = @Insuranceamount
    IF @Days >= 0 and @Days < 30 SET @_Current = @Insuranceamount

    UPDATE @tblAcAging 
    SET fOver180 = fOver180+ @_Over180 ,
    fOver120 = fOver120 + @_Over120 ,
    fOver90 = fOver90 + @_Over90 , 
    fOver60 = fOver60 + @_Over60 , 
    fOver30 = fOver30 + @_Over30 ,
    fCurrent = fCurrent + @_Current,
    fBalance = fCurrent + @_Current + fOver30 + @_Over30 + fOver60 + @_Over60 + fOver90 + @_Over90 + fOver120 + @_Over120 + fOver180+ @_Over180  
    WHERE fLocation = @mFlocation AND fAccount = @mFaccount AND fCategory = @mFcategory AND fType = 3;


    SkipTxPayPlan:
    FETCH NEXT FROM TempCursor INTO @mFlocation,@mFaccount,@mFPatient,@mFcategory,@mFtxnumber,@mFtxsuffix,@mFtxdate,@mForthogen,@mfDelete,@mePvt,@meIns,@mePayPvt,@mePvtCR,@mePvtDB,@mePayIns,@meInsCR,@meInsDB,@meOff,@meLast,@meFirst,@mePhone,@meColl ;
    END
    CLOSE TempCursor
    DEALLOCATE TempCursor

    END
    SELECT * FROM @tblAcAging t where t.fAccount = 966




    Please Mark as Answer if my post works for you or Vote as Helpful if it helps you. Kapil Singh

    Saturday, February 02, 2013 5:03 AM

Answers

  • The WHILE loop suggested by Alok is completely pointless. Often these "poor man's cursor" degrades performance even more, because people overlook that they need to add an index on the loop column in the table variable.

    A better solution is to change the cursor declaration to

       DECLARE cur CUSROR STATIC LOCAL FOR

    It's still a cursor, but at least you don't get the problems of the default cursor type, which is dynamic. And LOCAL makes sure that your cursor goes away if your procedure crashes half-way through.

    The real solution is of course as Lathseesh says: set-based, and I assume that the reason you posted in the first place was to get help with a set-based solution. Unfortunately, the logic seems a bit complex, although I have not dug into in detail. But it goes a little beyond what I'm prepared to attack right now.

    As a start, could you give a description in words of the business problem the procedure solves? Also, please include which version of SQL Server you are using. To wit, while set-based solution often is to prefer, there are classes of problems where you get best performance with iterative solution because of lack of support in SQL.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Saturday, February 02, 2013 10:41 AM
  • I have scanned through your current code, and it should be possible to convert it to a set based approach. However, it will be a lot of work for you (I doubt anyone in this forum is willing to do the work).

    The mind set you need for this set based approach, is to perform each step in your original code and apply it to every row in your cursor selection.

    For example, the first real action in your loop is something like:

    IF NOT EXISTS( ... )  -- test for one row
    BEGIN
      INSERT INTO @tblAcAging ( ... ) VALUES ( ... )  -- insert one row
    END

    With a set based approach, you would convert this to:

    INSERT INTO @tblAcAging ( ... )
    SELECT ...  -- many rows
    FROM ... -- probably your cursor query
    WHERE ... -- criteria you use now for inserting the cursor row

    As you can see, this will be a lot of work and a lot of testing. If this is the first time you attempt to rewrite a cursor loop to a set based approach, then I suggest you first train with a (much) simpler case then your current code...


    Gert-Jan

    Monday, February 04, 2013 8:30 PM

All replies

  • why don't you use WHILE loop instead of cursor..
    Saturday, February 02, 2013 5:45 AM
  • how to implement while loop that replace this cursor

    Please Mark as Answer if my post works for you or Vote as Helpful if it helps you. Kapil Singh

    Saturday, February 02, 2013 5:58 AM
  • first store the data into a temp table or table variable with an additional column rownumber. 2nd retreive the total count of records and spin the loop. implement all your business logic inside the loop.
    Saturday, February 02, 2013 6:03 AM
  • something like this...

    declare @tableForLoop as table
    	(
    		col1 ,
    		--
    		---
    		
    	)
    	INSERT INTO @tableForLoop
    	SELECT 
    	ROW_NUMBER(ORDER BY <COL>) AS RowNumber,
    	Account.Flocation,  
    	Account.Faccount,  
    	TxPayPlan.FPatient,  
    	TxPayPlan.Fcategory,  
    	TxPayPlan.Ftxnumber,  
    	TxPayPlan.Ftxsuffix,  
    	TxPayPlan.Ftxdate,  
    	Txpayplan.Forthogen,   
    	TxPayPlan.fDelete,  
    	ePvt = Case When (TxPayPlan.fContractNo = 0) Then Fpvtdue Else 0  End,  
    	eIns = TxPayPlan.Finsdue,  
    	ePayPvt = Sum(
    	Case When ((Payment.fPayType <=50)  
    		AND (Payment.finsurance <> 1)  
    		AND (PayDtl.fContractNo = 0)  
    		AND (Txpayplan.Finscode = PayDtl.FinsKode)  
    		AND (TxPayPlan.FPatient = PayDtl.FPatient)  
    		AND (PayDtl.FDelete <> 1)  And Payment.fDelete <> 1   
    		--AND (@rOrthoGen  IN (Payment.fOrthoGen,10))    
    		AND (Payment.fpaydate <= @AsOnDate )) 
    		Then PayDtl.fAmount Else 0 	End),  
    	ePvtCR = Sum(  Case When ((Payment.fpaytype > 50)   
    		AND (Payment.fpaytype <= 60)   
    		AND (Payment.finsurance <> 1)   
    		AND (PayDtl.fContractNo = 0)   
    		AND (Txpayplan.Finscode = PayDtl.FinsKode)   
    		AND (TxPAyPlan.FPatient = PayDtl.FPatient)   
    		AND (PayDtl.FDelete <> 1)  And Payment.fDelete <> 1  
    		--AND (@rOrthoGen  IN (Payment.fOrthoGen,10))    
    		AND (Payment.fpaydate <= @AsOnDate )) 
    		Then 
    		PayDtl.fAmount 
    		Else 
    		0 
    		End),  
    	ePvtDB = Sum(  Case When ((Payment.fpaytype > 60)  
    		AND (Payment.finsurance <>1)  
    		AND (PayDtl.fContractNo = 0)  
    		AND (Txpayplan.Finscode = PayDtl.FinsKode)  
    		AND (TxPAyPlan.FPatient = PayDtl.FPatient)  
    		AND (PayDtl.FDelete <> 1)  And Payment.fDelete <> 1  
    		--AND (@rOrthoGen  IN (Payment.fOrthoGen,10))    
    		AND (Payment.fpaydate <= @AsOnDate )) 
    		Then 
    		PayDtl.fAmount 
    		Else 
    		0 
    		End),  
    	ePayIns = Sum(
    		Case When ((payment.fpaytype <= 50)  
    		AND (Payment.finsurance = 1)   
    		AND (Txpayplan.Finscode = PayDtl.FinsKode)   
    		AND (TxPAyPlan.FPatient = PayDtl.FPatient)  
    		AND (PayDtl.FDelete <> 1)  And Payment.fDelete <> 1  
    		--AND (@rOrthoGen  IN (Payment.fOrthoGen,10))    
    		AND (Payment.fpaydate <= @AsOnDate )) 
    		Then 
    		PayDtl.fAmount 
    		Else 
    		0 
    		End),  
    	eInsCR = Sum(  Case When ((payment.fpaytype > 50)   
    		AND (Payment.fpaytype <= 60)  
    		AND (Payment.finsurance = 1)  
    		AND (Txpayplan.Finscode = PayDtl.FinsKode)  
    		AND (TxPAyPlan.FPatient = PayDtl.FPatient)   
    		AND (PayDtl.FDelete <> 1)  And Payment.fDelete <> 1  
    		--AND (@rOrthoGen  IN (Payment.fOrthoGen,10))    
    		AND (Payment.fpaydate <= @AsOnDate )) 
    		Then 
    		PayDtl.fAmount 
    		Else 
    		0 
    		End),  
    	eInsDB = Sum(  Case When ((Payment.fpaytype > 60)  
    		AND (Payment.finsurance = 1)   
    		AND (Txpayplan.Finscode = PayDtl.FinsKode)   
    		AND (TxPAyPlan.FPatient = PayDtl.FPatient)   
    		AND (PayDtl.FDelete <> 1)  And Payment.fDelete <> 1  
    		--AND (@rOrthoGen  IN (Payment.fOrthoGen,10))    
    		AND (Payment.fpaydate <= @AsOnDate )) 
    		Then 
    		PayDtl.fAmount 
    		Else 
    		0 
    		End),  
    	eOff = TxPayPlan.Foffice,  
    	eLast = Account.Flastname,  
    	eFirst = Account.Ffirstname,  
    	ePhone = Account.Fhomephone,  
    	eColl = Account.fCollSend
    	FROM 
    		Account 
    		INNER JOIN TxPayPlan 
    			ON  Account.Flocation = TxPayPlan.Flocation AND   
    			Account.Faccount = TxPayPlan.Faccount
    		LEFT JOIN PayDtl 
    			ON  TxPayPlan.Flocation = PayDtl.fLocation AND   
    			TxPayPlan.Faccount = PayDtl.fAccount AND   
    			TxPayPlan.Ftxnumber = PayDtl.fTxNumber
    		LEFT JOIN Payment 
    			ON  PayDtl.fLocation = Payment.fLocation AND   
    			PayDtl.fPayBatchNo = Payment.fPayBatchNo
    	WHERE 
    	--TxPayPlan.flocation = @rLocation  AND
    	--@rOffice IN (TxPayPlan.foffice,'')  
    	--AND @rCategory IN (TxPayPlan.fcategory,'')  
    	--AND @rOrthoGen  IN (TxPayPlan.fOrthoGen,10)  
    	--AND 
    		TxPayPlan.fDelete <> 1   
    	GROUP BY 
    		Account.Flocation,  
    		Account.Faccount,  
    		TxPayPlan.FPatient,   
    		TxPayPlan.Fcategory,   
    		TxPayPlan.Ftxnumber,   
    		TxPayPlan.Ftxsuffix,   
    		TxPayPlan.Ftxdate,   
    		TxPayPlan.Forthogen,   
    		TxPayPlan.fDelete,  
    		TxPayPlan.fContractNo,  
    		TxPayPlan.fInsdue,  
    		TxPayPlan.fPvtDue,   
    		TxPayPlan.fOffice,   
    		Account.fLastName,   
    		Account.fFirstName,   
    		Account.fHomePhone,  
    		Account.fCollSend  
    	ORDER BY 
    		Account.Faccount;  
    
    DECLARE @ProcessROwCount INT = 1
    		,@TotalRowCOunt INT
    
    SELECT @TotalRowCOunt = COUNT(*) FROM @TableForLooop
    
    WHILE @TotalRowCOunt > = @ProcessROwCount
    BEGIN
    	Your businss logic here
    	
    	SET @ProcessROwCount = @ProcessROwCount + 1
    END

    Saturday, February 02, 2013 6:08 AM
  • DO NOT USE WHILE . While also loops the data. You need to try a set based approach here.

    I dont be able to help on coding as issue with SSMS in my laptop. Google out Set basd approcah.You will get lots of example. And it suits your code also. You will get good performance definitely.


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Saturday, February 02, 2013 6:57 AM
  • I proceed in the manner that you told.

    But the variables that I declared in @tableforloop is not accessible in While loop business.


    Please Mark as Answer if my post works for you or Vote as Helpful if it helps you. Kapil Singh

    like 

    declare @PrivateAmount Money

    SET @PrivateAmount = (ISNULL(mePvt,0) + ISNULL(mepvtdb,0)) - (ISNULL(mepaypvt,0) + ISNULL(mepvtcr,0))

    these columns I declared in table variable. But it gives error of Invalid column name

    • Edited by Kapil_KK Saturday, February 02, 2013 7:01 AM
    Saturday, February 02, 2013 6:59 AM
  • The WHILE loop suggested by Alok is completely pointless. Often these "poor man's cursor" degrades performance even more, because people overlook that they need to add an index on the loop column in the table variable.

    A better solution is to change the cursor declaration to

       DECLARE cur CUSROR STATIC LOCAL FOR

    It's still a cursor, but at least you don't get the problems of the default cursor type, which is dynamic. And LOCAL makes sure that your cursor goes away if your procedure crashes half-way through.

    The real solution is of course as Lathseesh says: set-based, and I assume that the reason you posted in the first place was to get help with a set-based solution. Unfortunately, the logic seems a bit complex, although I have not dug into in detail. But it goes a little beyond what I'm prepared to attack right now.

    As a start, could you give a description in words of the business problem the procedure solves? Also, please include which version of SQL Server you are using. To wit, while set-based solution often is to prefer, there are classes of problems where you get best performance with iterative solution because of lack of support in SQL.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Saturday, February 02, 2013 10:41 AM
  • I have scanned through your current code, and it should be possible to convert it to a set based approach. However, it will be a lot of work for you (I doubt anyone in this forum is willing to do the work).

    The mind set you need for this set based approach, is to perform each step in your original code and apply it to every row in your cursor selection.

    For example, the first real action in your loop is something like:

    IF NOT EXISTS( ... )  -- test for one row
    BEGIN
      INSERT INTO @tblAcAging ( ... ) VALUES ( ... )  -- insert one row
    END

    With a set based approach, you would convert this to:

    INSERT INTO @tblAcAging ( ... )
    SELECT ...  -- many rows
    FROM ... -- probably your cursor query
    WHERE ... -- criteria you use now for inserting the cursor row

    As you can see, this will be a lot of work and a lot of testing. If this is the first time you attempt to rewrite a cursor loop to a set based approach, then I suggest you first train with a (much) simpler case then your current code...


    Gert-Jan

    Monday, February 04, 2013 8:30 PM