Post reply

how to eliminate a cursor

  • 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

  • July 19, 2005 at 10:31 am

    #575562

    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

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

  • July 19, 2005 at 10:34 am

    #575563

    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.

  • July 19, 2005 at 11:20 am

    #575578

    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

  • July 19, 2005 at 11:46 am

    #575592

    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

  • July 19, 2005 at 12:04 pm

    #575605

    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

  • July 19, 2005 at 1:58 pm

    #575647

    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