unable to understand this result of Cursors in sql server

Question

Post reply

unable to understand this result of Cursors in sql server

Rohit Singh

SSC Enthusiast

Points: 131
More actions
December 8, 2004 at 1:07 am

#86108
I have designed a query, in
1. first i fetch a record from an absolute position in cursor.
2. Then inserting that record in temporary table .
3. Then checking the status of fetch fetch other records also.
But i am getting the last record repeated twice.
And if i insert after checking the status , no last repeated row.
The first query is given below.. and its result
EmailId
abc@abc.abc
kansal@a.com
xyz@xyz.com
xyz@xyz.com

    SET NOCOUNT ON
    DECLARE @rowcount as int
    DECLARE @TOTFETCH as int
    SET @ROWCOUNT=0


    DECLARE @ID AS VARCHAR(200)
    DECLARE @CLIENTID AS VARCHAR(200)
    DECLARE @EmailId AS VARCHAR(200)
    DECLARE @Salutation AS VARCHAR(200)
    DECLARE @FirstName AS VARCHAR(200)
    DECLARE @LastName AS VARCHAR(200)
    DECLARE @CompanyName AS VARCHAR(200)
    DECLARE @ContactName AS VARCHAR(200)
    DECLARE @Address1 AS VARCHAR(200)
    DECLARE @Address2 AS VARCHAR(200)
    DECLARE @City AS VARCHAR(200)
    DECLARE @State AS VARCHAR(200)
    DECLARE @Country AS VARCHAR(200)
    DECLARE @Zip AS VARCHAR(200)
    DECLARE @ContactNo AS VARCHAR(200)
    DECLARE @Fax AS VARCHAR(200)
    DECLARE @active AS VARCHAR(200)
    DECLARE @MailType AS VARCHAR(200)
    DECLARE @status AS VARCHAR(200)
    DECLARE @Bounced AS VARCHAR(200)
    DECLARE @CClientid AS VARCHAR(200)
    DECLARE @ABVAL AS INT
    DECLARE @FLAG AS iNT
    set @FLAG = 0

    SET @ABVAL = 1
    SET @TOTFETCH =40000

    CREATE TABLE #CLIENT
     (EmailId VARCHAR(200))

    DECLARE CURR CURSOR SCROLL FOR
    SELECT CLIENTADDRESSBOOK.EmailId FROM CLIENTADDRESSBOOK INNER JOIN GROUPMAPPING ON GROUPMAPPING.CLIENTID = CLIENTADDRESSBOOK.CLIENTID AND CLIENTADDRESSBOOK.EMAILID = GROUPMAPPING.EMAILID INNER JOIN MAILGROUPS ON MAILGROUPS.CLIENTID = CLIENTADDRESSBOOK.CLIENTID
     AND MAILGROUPS.GROUPID = GROUPMAPPING.GROUPID WHERE MAILGROUPS.CLIENTID = 472 AND MAILGROUPS.GROUPID IN (2) and CLIENTADDRESSBOOK.ACTIVE = 'y'

    OPEN CURR
    FETCH ABSOLUTE @ABVAL FROM CURR
      INTO @EmailId

     INSERT INTO #CLIENT SELECT
      @EmailId

    WHILE @@FETCH_STATUS = 0 AND @ROWCOUNT < @TOTFETCH

    BEGIN
     SET @ROWCOUNT = @ROWCOUNT + 1


       FETCH NEXT FROM CURR
       INTO
   @EmailId

    INSERT INTO #CLIENT SELECT
   @EmailId

    END
    SELECT
    EmailId FROM #CLIENT
    CLOSE CURR
    DEALLOCATE CURR
    DROP TABLE #CLIENT
    SET NOCOUNT OFF

and second query:
abc@abc.abc
kansal@a.com
xyz@xyz.com
    SET NOCOUNT ON
    DECLARE @rowcount as int
    DECLARE @TOTFETCH as int
    SET @ROWCOUNT=1


    DECLARE @ID AS VARCHAR(200)
    DECLARE @CLIENTID AS VARCHAR(200)
    DECLARE @EmailId AS VARCHAR(200)
    DECLARE @Salutation AS VARCHAR(200)
    DECLARE @FirstName AS VARCHAR(200)
    DECLARE @LastName AS VARCHAR(200)
    DECLARE @CompanyName AS VARCHAR(200)
    DECLARE @ContactName AS VARCHAR(200)
    DECLARE @Address1 AS VARCHAR(200)
    DECLARE @Address2 AS VARCHAR(200)
    DECLARE @City AS VARCHAR(200)
    DECLARE @State AS VARCHAR(200)
    DECLARE @Country AS VARCHAR(200)
    DECLARE @Zip AS VARCHAR(200)
    DECLARE @ContactNo AS VARCHAR(200)
    DECLARE @Fax AS VARCHAR(200)
    DECLARE @active AS VARCHAR(200)
    DECLARE @MailType AS VARCHAR(200)
    DECLARE @status AS VARCHAR(200)
    DECLARE @Bounced AS VARCHAR(200)
    DECLARE @CClientid AS VARCHAR(200)
    DECLARE @ABVAL AS INT
    DECLARE @FLAG AS iNT
    set @FLAG = 0

    SET @ABVAL = 1
    SET @TOTFETCH =40000

    CREATE TABLE #CLIENT
     (EmailId VARCHAR(200))

    DECLARE CURR CURSOR SCROLL FOR
    SELECT CLIENTADDRESSBOOK.EmailId FROM CLIENTADDRESSBOOK
     INNER JOIN GROUPMAPPING ON GROUPMAPPING.CLIENTID = CLIENTADDRESSBOOK.CLIENTID AND
     CLIENTADDRESSBOOK.EMAILID = GROUPMAPPING.EMAILID
     INNER JOIN MAILGROUPS ON MAILGROUPS.CLIENTID = CLIENTADDRESSBOOK.CLIENTID
     AND MAILGROUPS.GROUPID = GROUPMAPPING.GROUPID WHERE MAILGROUPS.CLIENTID = 472 AND MAILGROUPS.GROUPID IN (2) and CLIENTADDRESSBOOK.ACTIVE = 'y'

    OPEN CURR

    FETCH ABSOLUTE @ABVAL FROM CURR

      INTO @EmailId


    WHILE @@FETCH_STATUS = 0 AND @ROWCOUNT < @TOTFETCH
    BEGIN
    INSERT INTO #CLIENT SELECT @EmailId
     SET @ROWCOUNT = @ROWCOUNT + 1
     FETCH NEXT FROM CURR INTO @EmailId

    END

    SELECT
    EmailId FROM #CLIENT
    CLOSE CURR
    DEALLOCATE CURR
    DROP TABLE #CLIENT
    SET NOCOUNT OFF

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

sadheesh kumar m Old Hand Points: 385 More actions · Answer 1

abc@abc.abc

kansal@a.com

xyz@xyz.com

First Query

Step 1

FETCH ABSOLUTE @ABVAL FROM CURR INTO @EmailId

After this the Cursor Position in abc@abc.abc

Inserted this mail id in temp table

Step 2

Check the Status : Not Zero

Step 3

FETCH NEXT FROM CURR INTO @EmailId

Now Only Cur Position in kansal@a.com record

Inserted this mail id in temp table

Step 4

Check the Fetch Status : Status not Zero the cursor position un kansal@a.com record

Step 5

FETCH NEXT FROM CURR INTO @EmailId

Now Cursor position in xyz@xyz.com record

Inserted this mail id in temp table

Step 6

Check the Fetch Status : Status not Zero the cursor position un xyz@xyz.com record

FETCH NEXT FROM CURR INTO @EmailId

No more record in that So Now only the Fetch Status is zero.

But Inserted this mail id in temp table

So that only repeadted in this query

Second Query

Step 1

FETCH ABSOLUTE @ABVAL FROM CURR INTO @EmailId

Check the fetch status : Fetche status not zero.

Now the Cursor Position in abc@abc.abc

Inserted this mail id in temp table

Step 2

FETCH NEXT FROM CURR INTO @EmailId

Cursor position in kansal@a.com record

Check the Status : Not Zero

Step 3

Inserted this mail id in temp table

FETCH NEXT FROM CURR INTO @EmailId

Cursor position in xyz@xyz.com record

Check the Status : Not Zero

Step 4

Inserted this mail id in temp table

FETCH NEXT FROM CURR INTO @EmailId

No more record . The fetch status is zero now.

Check the Status : Zero

exit from the loop

Rohit Singh SSC Enthusiast Points: 131 More actions · Answer 2

Rohit Singh

SSC Enthusiast

Points: 131

December 8, 2004 at 5:06 am

#533010

thanks a lot sadheesh for clarifying this query.