How to supress cursor select messages

  • I would like to suppress the cursor select messages.  Take for instance this code:

    SET NOCOUNT ON

    CREATE TABLE #bob(

      tbl_id integer PRIMARY KEY NOT NULL

    )

    INSERT INTO #bob(tbl_id) VALUES(1)

    INSERT INTO #bob(tbl_id) VALUES(2)

    INSERT INTO #bob(tbl_id) VALUES(3)

    INSERT INTO #bob(tbl_id) VALUES(4)

    INSERT INTO #bob(tbl_id) VALUES(5)

    INSERT INTO #bob(tbl_id) VALUES(6)

    INSERT INTO #bob(tbl_id) VALUES(7)

    INSERT INTO #bob(tbl_id) VALUES(8)

    INSERT INTO #bob(tbl_id) VALUES(9)

    INSERT INTO #bob(tbl_id) VALUES(10)

    DECLARE @pk integer

    DECLARE cur_row CURSOR

      FOR

        SELECT b.tbl_id

        FROM #bob b

      FOR UPDATE

    OPEN cur_row

    FETCH NEXT FROM cur_row INTO @pk

    /* loop through each attachment row */

    WHILE (@@FETCH_STATUS=0) BEGIN

      PRINT convert(varchar(23),getdate(),126)

      /* get next attachment row */

       FETCH NEXT FROM cur_row

    END /* cursor loop */

    /* close and deallocate cursor */

    CLOSE cur_row

    DEALLOCATE cur_row

    DROP TABLE #bob

    The restults are:

    2005-02-07T17:34:06.507

    tbl_id     

    -----------

    2

    2005-02-07T17:34:06.507

    tbl_id     

    -----------

    3

    2005-02-07T17:34:06.507

    tbl_id     

    -----------

    4

    2005-02-07T17:34:06.507

    tbl_id     

    -----------

    5

    2005-02-07T17:34:06.507

    tbl_id     

    -----------

    6

    2005-02-07T17:34:06.507

    tbl_id     

    -----------

    7

    2005-02-07T17:34:06.507

    tbl_id     

    -----------

    8

    2005-02-07T17:34:06.517

    tbl_id     

    -----------

    9

    2005-02-07T17:34:06.517

    tbl_id     

    -----------

    10

    2005-02-07T17:34:06.517

    tbl_id     

    -----------

     

    How can I get it to not show the tbl_id result sets so that only the datetime stamp shows in the Query Analyzer Results window? 

    Thanks!

    P.S. Telling me why I shouldn't be using cursors will not answer my question

  • You're missing the "INTO @pk" part on your second FETCH statement.

     

    --------------------
    Colt 45 - the original point and click interface

  • I figured it was something simple like that!!  Thanks for your help!!

  • Your best bet is of course ***not to use cursors***. I have been a DBA for over 5 years and the number of cursors I have needed to used I could count on one hand if I had 2 finger amputated. The example you provided is a great example of just exactly when you don't need a cursor. Hey, but don't take my word for it, Ken Henderson says essentially the same thing in his Guru's Guide books.

    You can almost always use a WHILE loop.

    Here's a piece of code I found a couple of years back which is a good example of using a while loop instead of a cursor:

     

    /* SQL Server Cursorless Cursor  *//* http://www.eggheadcafe.com/PrintSearchContent.asp?LINKID=529 */declare @rc intdeclare @RowCnt int declare @MaxRows int declare @Email nvarchar(255) select @RowCnt = 1      declare @Import table   (     rownum int IDENTITY (1, 1) Primary key NOT NULL ,     Email nvarchar(255)    )   insert into @Import (Email) values ('blah@blah.com')   insert into @Import (Email) values ('blahblah@blah.com')   select @MaxRows=count(*) from @Import      while @RowCnt <= @MaxRows   begin     select @rc=0        select @Email = Email       from @Import        where rownum = @RowCnt      print @Email               Select @RowCnt = @RowCnt + 1   end

    G. Milner

  • /* SQL Server Cursorless Cursor  */

    /* http://www.eggheadcafe.com/PrintSearchContent.asp?LINKID=529 */

    declare @rc int

    declare @RowCnt int

    declare @MaxRows int

    declare @Email nvarchar(255)

    select @RowCnt = 1

      

      declare @Import table

      (

        rownum int IDENTITY (1, 1) Primary key NOT NULL ,

        Email nvarchar(255)

       )

      insert into @Import (Email) values ('blah@blah.com')

      insert into @Import (Email) values ('blahblah@blah.com')

      select @MaxRows=count(*) from @Import

      

      while @RowCnt <= @MaxRows

      begin

        select @rc=0

      

        select @Email = Email

          from @Import

           where rownum = @RowCnt

         print @Email

            

         Select @RowCnt = @RowCnt + 1

      end

    -- sorry, that last didn't post right. Try this.

    G. Milner

  • i love cursors please use cursors

    <a href="http://www.websolsoftware.com"> For IT jobs click here</a>

    *Sukhoi*[font="Arial Narrow"][/font]

  • The example you provided is a great example of just exactly when you don't need a cursor.

    No, the example I provided showed a great example of the problem I was having.  It showed nothing of what I was actually doing with a cursor.

    I'm glad everyone is on board with the cursors are bad ideal, but don't be so quick to judge if you don't know the application.  Even Ken would agree with that.

     

  • Of course, the real trick is to replace the cursor solution with a set based solution.....not just trading one loop for another!

    Gordon Pollokoff

    "Wile E. is my reality, Bugs Bunny is my goal" - Chuck Jones

Viewing 8 posts - 1 through 7 (of 7 total)

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