Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Mar 2004
    Posts
    74

    Unanswered: How can i get rowcount value of cursor ?

    Good day All,

    i have issue on store proc with cursor, i would like to get the count of the record in table_A , but both @@CURSOR_ROWS and @@ROWCOUNT doesn't give me the correct record count

    Code:
    DECLARE @mycursor CURSOR
    
    SET @mycursor = CURSOR FOR 
    SELECT * FROM TABLE_A
    
    SELECT @@CURSOR_ROWS AS 'RowReturn'
    SELECT @@ROWCOUNT AS 'COUNT'
    thank you !

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    I have better questions and maybe some real time to help you today.

    1. why are you using a cursor?
    2. why do you need the row count?

    I can provide you a few answers to your question but I need to make sure it is the best answer for your situation or I might be doing you a disservice. You might want to take a look at the sticky at the top of the forum regarding how to best state your problem.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Mar 2004
    Posts
    74
    Hi Thrasymachus,

    i need the cursor because i need to check record by record to perform whether update or insert action required in another table.

    ie : i query Table_A with 10 records, then these 10 records need to check table_B (as master table) in record by record basis whether there are existing record, if there are then perform update else insert ... and the case of no record in table_A, then i perform nothing, so i need rowcount to check whether the record exist,

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    why use a cursor? they can be slow and cumbersome. why not something like...

    Code:
    UPDATE T1 SET SOMEFIELD = 'SOMETHING'
    FROM TABLE1 T1
    JOIN TABLE2 T2
    ON T1.THISFIELD = T2.THATFIELD
    WHERE T1.THIS = 'THAT'
    
    IF @@ROWCOUNT = 0
    BEGIN
    	INSERT INTO TABLEWHATEVER(SOMESTUPIDDATA)
    	VALUES('Bunch Of Lies')
    END
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  5. #5
    Join Date
    Mar 2004
    Posts
    74
    sound great Thrasymachus ...

    but the records i query out , it might mix with insert and update action ....it is not pure update or insert , in this case , we take out if statement

    just for knowledge, how to we get the count from cursor in first thread sample?

  6. #6
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    I am not helping you anymore until you post your code.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  7. #7
    Join Date
    Mar 2004
    Posts
    74
    Hi Thrasymachus,

    this is something i plan to do,

    Code:
    DECLARE @get_allrec_cursor CURSOR
    	
    SET  @get_allrec_cursor = CURSOR FOR
    		SELECT column1, column2 FROM TABLE_A
    
    	
    open @get_allrec_cursor
    	
    if(@@ROWCOUNT <> 0)
    	BEGIN	
    		WHILE (@@FETCH_STATUS = 0)
    		BEGIN
                                         -- not yet complete the logic yet
    			exec sp_insert column1, column2
    			exec sp_update column1, column2
    		END
    	END
    
    CLOSE @get_allrec_cursor
    DEALLOCATE @get_allrec_cursor

  8. #8
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    you are going to make me work for it today.

    what do sp_insert and sp_update look like?

    what I am trying to get at is that your code will be faster and more efficient if you use set based processing instead of loops in your database code. There is almost always a set based solution and it is faster and more efficient 95% of the time.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  9. #9
    Join Date
    Mar 2004
    Posts
    74
    Hi Thrasymachus,

    help me to understand on what set based you are mentioning ?

    i haven't figure out the sp_insert/update , basically the query involve some logic/criteria that allow insert/update.

    in the previous sample script, how could you get the exact rowcount ?

    thank you for your time and guidance !

  10. #10
    Join Date
    Apr 2007
    Posts
    183
    Do an UPSERT operation. Or MERGE if you are using SQL Server 2008.

    BEGIN TRAN

    UPDATE t
    SET t.Col1 = s.Col1, t.Col2 = s.Col2
    FROM Target AS t
    INNER JOIN Source AS s ON s.pkCol = t.pkCol

    INSERT Target (Col1, Col2)
    SELECT s.Col1, s.Col2
    FROM Source AS s
    LEFT JOIN Target AS t ON t.pkCol = s.pkCol
    WHERE t.pkCol IS NULL

    COMMIT TRAN

  11. #11
    Join Date
    Mar 2004
    Posts
    74
    thanks Peso for the suggestion, do you have idea on how we get rowcount from cursor as well ?

  12. #12
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Borrowing from Peso's example, you could use:
    Code:
    DECLARE @i		INT
    
    BEGIN TRAN
    
    UPDATE t
       SET t.Col1 = s.Col1
    ,     t.Col2 = s.Col2
       FROM Target AS t
       INNER JOIN Source AS s
          ON s.pkCol = t.pkCol
    
    SET @i = @@rowcount
    
    INSERT Target (Col1, Col2)
       SELECT s.Col1, s.Col2
          FROM Source AS s
          LEFT JOIN Target AS t
             ON t.pkCol = s.pkCol
          WHERE t.pkCol IS NULL
    
    SET @i = @i + @@rowcount
    
    COMMIT TRAN
    
    SELECT @i AS 'rows affected'
    -PatP

  13. #13
    Join Date
    Apr 2007
    Posts
    183
    And look! No cursor. Only speed! And error handling!

    Code:
    DECLARE @i INT,
    	@e INT
    
    BEGIN TRAN
    
    UPDATE t
       SET t.Col1 = s.Col1
    ,     t.Col2 = s.Col2
       FROM Target AS t
       INNER JOIN Source AS s
          ON s.pkCol = t.pkCol
    
    SELECT @i = @@rowcount, @e = @@error
    
    if @@ERROR = 0
    begin
    	INSERT Target (Col1, Col2)
    	   SELECT s.Col1, s.Col2
    	      FROM Source AS s
    	      LEFT JOIN Target AS t
    		 ON t.pkCol = s.pkCol
    	      WHERE t.pkCol IS NULL
    
    	SELECT @i = @i + @@rowcount, @e=@@error
    end
    
    if @error <> 0
    begin
    	rollback tran
    	set @i = 0
    else
    	COMMIT TRAN
    
    SELECT @i AS 'rows affected'
    Last edited by Peso; 10-12-08 at 16:37.

  14. #14
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Oh those show offs!

    -PatP

  15. #15
    Join Date
    Mar 2004
    Posts
    74
    thanks All on guidance !

    this is great learning curve on my side ....appreciated your warm input

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •