Cursor fetch location

Question

0.00/5 (No votes)

See more:

I have a cursor as follows:

SQL

DECLARE trancur cursor FAST_FORWARD For 
         SELECT 
               t.ExpCom*t2.SplitPerc, 
               t.TrNo 
          FROM #TmpAgtTot2 t2 (NOLOCK) 
          Inner Join #TmpTran t (NOLOCK) ON t.RepCode=t2.TranRepCode 
          Where  t.ASL in (''A'', ''S'', ''L'')

Then next I do:

SQL

Fetch trancur INTo @NextGrossComm, @TrNo
	SELECT @CurrGrossComm = @CurrGrossComm + IsNull(@NextGrossComm,0)

before this:

SQL

While @@fetch_status = 0 AND @CurrGrossComm < @MaxGrossComm
	BEGIN
              Set @myflag  = 1
              @CurrGrossComm = @CurrGrossComm + IsNull(@NextGrossComm,0)

        End

My Question is, is @NextGrossComm populated with a value from the cursor for the underlined code or that will not happen until I enter the while loop? If it has a value from the cursor, then which record does it have - Is it the 1st record?

Posted 24-Oct-13 8:51am

bassofa1

Updated 24-Oct-13 8:55am

phil.o

v3

Add a Solution

Comments

bassofa1 24-Oct-13 15:22pm

So if you look the underlined code, will you say there is data in the @NextGrossComm even before it entered the while loop?

1 solution

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Mike Meinz · Accepted Answer · 2013-10-24T09:15:00

Solution 1

The Fetch retrieves the data. You do not want to use the data until within the While loop in case there is no data returned. Also, you need a FETCH NEXT at the bottom of the While loop.

SQL

DECLARE trancur cursor FAST_FORWARD For
         SELECT
               t.ExpCom*t2.SplitPerc,
               t.TrNo
          FROM #TmpAgtTot2 t2 (NOLOCK)
          Inner Join #TmpTran t (NOLOCK) ON t.RepCode=t2.TranRepCode
          Where  t.ASL in (''A'', ''S'', ''L'')

Fetch FIRST trancur INTO @NextGrossComm, @TrNo
While @@fetch_status = 0 AND @CurrGrossComm < @MaxGrossComm
    BEGIN
         Set @myflag  = 1
         Set @CurrGrossComm = @CurrGrossComm + IsNull(@NextGrossComm,0)
         Fetch NEXT trancur INTO @NextGrossComm, @TrNo
    END

Posted 24-Oct-13 9:15am

Mike Meinz

Comments

bassofa1 25-Oct-13 6:19am

Well,I think you re-created my code and answered it your own way -I am just analyzing an existing code and have a doubt about the underlined portion of the code I posted. I just want to know if @NextGrossComm contains a value from the cursor at this point.

Mike Meinz 25-Oct-13 8:37am

I corrected the code that you provided. The underlined portion should not be there. Values retrieved via Fetch should not be accessed before testing if the Fetch was successful. My code does that. Yours does not. Also, your code was missing the Fetch Next.

Do not access @NextGrossComm or @TrNo until within the While @@fetch_status = 0 loop. @NextGrossComm may or may not contain a value at the point of the underlined statement. You need to check @@fetch_status and if @@fetch_status=0 then you can count on @NextGrossComm having a value. That is what my version of your code does.

bassofa1 28-Oct-13 6:21am

Ok, If I understand you clearly, there is no impact on the value that's being assigned even before the while.So in effect the underlined portion is not necessary. I think you have sufficiently answered my question. thanks.

Cursor fetch location

1 solution

Solution 1

Add your solution here

Preview 0

Cursor fetch location

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us