How to supress cursor select messages

Question

Post reply

How to supress cursor select messages

JT Lovell

Ten Centuries

Points: 1238
More actions
February 7, 2005 at 3:50 pm

#68045

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

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

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

philcart SSC-Forever Points: 47797 More actions · Answer 1

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

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

JT Lovell Ten Centuries Points: 1238 More actions · Answer 2

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

Greg Milner SSCrazy Points: 2425 More actions · Answer 3

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

Greg Milner SSCrazy Points: 2425 More actions · Answer 4

/* 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

sukhoi971 SSCarpal Tunnel Points: 4833 More actions · Answer 5

i love cursors please use cursors

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

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

JT Lovell Ten Centuries Points: 1238 More actions · Answer 6

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.

Gordon Pollokoff SSCommitted Points: 1930 More actions · Answer 7

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