February 7, 2005 at 3:50 pm
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
February 7, 2005 at 4:43 pm
I figured it was something simple like that!! Thanks for your help!!
February 8, 2005 at 12:05 am
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
February 8, 2005 at 12:07 am
/* 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
February 8, 2005 at 4:37 am
i love cursors please use cursors
<a href="http://www.websolsoftware.com"> For IT jobs click here</a>
*Sukhoi*[font="Arial Narrow"][/font]
February 8, 2005 at 8:10 am
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.
February 8, 2005 at 12:46 pm
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