how to eliminate a cursor

  • 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

  • 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

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

  • 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.

  • 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

  • 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

  • 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

  • Sure!

    That's how we all learn

     


    * Noel

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

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