how to eliminate a cursor

Question

how to eliminate a cursor

Nancy Simon

SSC Veteran

Points: 216
More actions
July 19, 2005 at 10:14 am

#165392

I am trying to setup a procedure to stop all traces (max 4). I've figured out how to do this with a cursor, but in keeping with the "cursor is evil" idea, I'd like to do this differently. Any ideas?
Here's my code:
create procedure change_status_traces @status int as
DECLARE @traceid int
DECLARE traceid_cursor CURSOR FOR
SELECT distinct traceid from ::fn_trace_getinfo(default)
OPEN traceid_cursor
FETCH NEXT FROM traceid_cursor
INTO @traceid
WHILE @@FETCH_STATUS = 0
BEGIN
   execute sp_trace_setstatus @traceid, @status
   FETCH NEXT FROM traceid_cursor
   INTO @traceid
END
CLOSE traceid_cursor
DEALLOCATE traceid_cursor
GO
Thanks,
Nancy

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

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

DCPeterson SSCoach Points: 15266 More actions · Answer 1

I fully agree with the cursors are bad mentality, but in this case you don't have much of a choice. About the only other option is to create dynamic sql and since we probably aren't dealing with large numbers of traces the cursor is probably your best alternative.

/*****************

If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek

*****************/

Ste_P SSCarpal Tunnel Points: 4430 More actions · Answer 2

Hi Nancy,

There is always the 'cursorless loop' in your situation:-

Declare @WorkingVariable, @EndVariable

SET @EndVariable = SELECT MAX(distinct traceid)from ::fn_trace_getinfo(default)

SET @WorkingVariable= SELECT MIN(distinct traceid)from ::fn_trace_getinfo(default)

WHILE @WorkingVariable <= @EndVariable

BEGIN

--DO THE WORK

--INCREMENT BEFORE TESTING

SET @WorkingVariable= SELECT MIN(distinct traceid)from ::fn_trace_getinfo(default) WHERE traceid > @WorkingVariable

END

Not too sure if this is actually any faster but it eliminates the use of cursors. I'm sure someone else (maybe 'the bulldozer' ) will come up with a set based solution - but i'm not in that ballpark yet......

Have fun

Steve

We need men who can dream of things that never were.

Nancy Simon SSC Veteran Points: 216 More actions · Answer 3

Thanks for the suggestions. I've seen solutions on this site before which use a select statement to do some complex things (for example: select @output = @output & "," & input from ...) but were unable to get this to work with a stored procedure. I was hoping someone more experienced than me would know how.

- Nancy

noeld SSC Guru Points: 96590 More actions · Answer 4

Nancy,

Your solution is fine. Just for the fun of it

is this what you were talking about

create procedure change_status_traces @status int as

begin

declare @STR varchar(8000)

select @STR = Isnull(@str,'') + ' execute sp_trace_setstatus @traceid = '+ cast(traceid as varchar(20)) + ', @status = ' + cast(@status as varchar(20)) + char(13)+ Char(10)

from (SELECT distinct traceid from ::fn_trace_getinfo(default) ) derived

exec (@str)

end

note: this is not going to provide you with any benefits though!!!

Cheers

* Noel

Nancy Simon SSC Veteran Points: 216 More actions · Answer 5

Very cool! I hadn't thought of building a statement and then executing it. I was trying to pass the value directly to sp_trace_setstatus and it just didn't like me.

I think you are right, I'm best off with the script I have, but I will definitely file away the technique for later use.

Thanks,

Nancy

noeld SSC Guru Points: 96590 More actions · Answer 6

noeld

SSC Guru

Points: 96590

July 19, 2005 at 1:58 pm

#575647

Sure!

That's how we all learn

* Noel