November 16, 2011 at 5:54 pm
Hi all,
I've got a code that simply loops through rows in a table using cursors. I've noticed for each returned row, it's being displayed in SMSS.
Is there a way to avoid displaying every row returned from the cursor?
begin
SET XACT_ABORT ON
set nocount on
DECLARE curemployee CURSOR FOR SELECT BusinessEntityID FROM HumanResources.Employee2
BEGIN TRANSaction
OPEN curemployee
FETCH FROM curemployee
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH FROM curemployee
END
CLOSE curemployee
DEALLOCATE curemployee
end
GO
November 16, 2011 at 7:33 pm
If you FETCH INTO a @BusinessEntityID variable, SSMS will not display anything by default:
BEGIN
SET XACT_ABORT ON
SET NOCOUNT ON
DECLARE @BusinessEntityID INT -- Or VARCHAR(128), Perhaps?
DECLARE curemployee CURSOR FOR SELECT BusinessEntityID FROM HumanResources.Employee2
OPEN curemployee
FETCH FROM curemployee INTO @BusinessEntityID
WHILE @@FETCH_STATUS = 0
BEGIN
/* Do Something Here: Print? Select? Then... */
FETCH NEXT FROM curemployee INTO @BusinessEntityID
END
CLOSE curemployee
DEALLOCATE curemployee
END
GO
November 17, 2011 at 6:49 am
ahmed_b72 (11/16/2011)
Is there a way to avoid displaying every row returned from the cursor?
The best way is to avoid using a cursor in the first place. They perform horribly and can almost always be rewritten using a set-based approach that performs much better.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
November 17, 2011 at 8:17 am
drew.allen (11/17/2011)
ahmed_b72 (11/16/2011)
Is there a way to avoid displaying every row returned from the cursor?The best way is to avoid using a cursor in the first place. They perform horribly and can almost always be rewritten using a set-based approach that performs much better.
Drew
+1 for this. If you let us know what you need to accomplish I am sure someone will be able to provide a set-based solution. Most of the regulars on SSC make it our mission to eliminate RBAR (Row By Agonizing Row) wherever and whenever we can.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 17, 2011 at 3:29 pm
Got it.. thanks a lot.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply