I''d like to have this anaylyzed.

  • I made this small code fragment to practice... please teach me if there is something wrong with it and what I can do to make it better.

     

    ------------------------------------------------

    CREATE PROCEDURE pr_renameStock(

      @date datetime = null) AS

    begin

    DECLARE @item_no numeric

    DECLARE @name  nvarchar(500)

    DECLARE @error_code numeric

    DECLARE @cnt  numeric

    DECLARE @STR  varchar(4)

     

    DECLARE cursor1 CURSOR LOCAL STATIC READ_ONLY

     FOR SELECT ITEM_NO FROM T_STOCKS

    if @date = null

     set @date = GetDAte()

    print 'Update Time is ' + CONVERT(  varchar(100), @date,112)

    OPEN cursor1

    set @error_code = 0

    set @cnt = 0

    if @@cursor_rows = 0

    begin

     set @error_code = 1 -- No Records

     goto CLEANUP

    end

    while ( 1 = 1)

    begin

     FETCH NEXT FROM cursor1 INTO @item_no

     if @@fetch_status <> 0 

      break

     DECLARE cursor_name CURSOR LOCAL STATIC READ_ONLY

     FOR SELECT NAME FROM T_STOCKS WHERE ITEM_NO = @item_no

     OPEN cursor_name

     

     if @@cursor_rows = 0 

      goto NODETAIL

     FETCH NEXT FROM cursor_name INTO @name

     if @@fetch_status <> 0

      goto NODETAIL

     UPDATE T_STOCKS SET NAME = REPLICATE(  CHAR(65 + @cnt )  , 4)   WHERE ITEM_NO =  @item_no

     set @cnt = @cnt + 2

     

     if @@error <> 0

     begin

      deallocate cursor_name

      set @error_code = 3 -- error update

      break 

     end

    RESET:

     deallocate cursor_name

    end

    goto CLEANUP

    return

    NODETAIL:

     print 'no detail'

     goto RESET

    CLEANUP:

     

     if @error_code = 1

      print ' No Records'

     else if @error_code = 2

      print 'did not fetch'  

     else if @error_code = 3

      print ' error update'

     deallocate cursor1

    end

    GO

  • - Avoid using a cursor !!

    Is this what you aim for ?

    Declare @MyError int, @MyRowcount int

       Update S

     SET NAME = REPLICATE(  CHAR(65 + (Sc.Counter *2) )  , 4) 

     

       from T_STOCKS S

     inner join (Select ITEM_NO, count(*) as Counter

       from T_STOCKS S1

       Inner Join T_STOCKS S2

       on S1.ITEM_NO > S2.ITEM_NO

       group by S1.ITEM_NO ) Sc

     on S.ITEM_NO = Sc.ITEM_NO

    Select @MyError = @@error, @MyRowcount = @@Rowcount

    If @MyError = 0

     print ' error update ['  + cast(@MyError as varchar(15)) + ']'

    else

      begin

       

      print case @MyRowcount when 0 then 'No'

            else cast(@MyRowcount as varchar(15))

        end

      + ' Rows Updated '

      end

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution πŸ˜€

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • Great advice for everyone new (and sometimes old), stop using cursors unless they're absolutely necessary (I've written 3 in the last 6 years and that's too many).

    A set based approach is normally so much quicker you wont beleive it.

  • what's wrong with cursors then?

  • BTW, alzdba  your TSQL is really cool.. I would never have thought about that.. up till now I'm still figuring it out.. thanks a lot.

  • Declare @MyError int, @MyRowcount int

    Update S

     SET NAME = REPLICATE(  CHAR(65 + (Sc.Counter *2) )  ,4) 

       from T_STOCKS S

     inner join (Select S1.ITEM_NO, count(*) as Counter

       from T_STOCKS S1

       Inner Join T_STOCKS S2

       on S1.ITEM_NO >= S2.ITEM_NO

       group by S1.ITEM_NO ) Sc

     on S.ITEM_NO = Sc.ITEM_NO

    Select @MyError = @@error, @MyRowcount = @@Rowcount

    If @MyError = 0

     print ' error update ['  + cast(@MyError as varchar(15)) + ']'

    else

      begin

       

      print case @MyRowcount when 0 then 'No'

            else cast(@MyRowcount as varchar(15))

        end

      + ' Rows Updated '

      end

     

    --------------------------------------------------------------------

    I've made a correction since it won't update the first row.. GREAT CODE BTW.. I'm amazed master.. (way too complex for me already.. :wow

  • ThΓ© big thing with any RDBMS (sqlserver/oracle/db2/...)  is to work set-oriented. Once you get that in the fingers, things will work smoothly.

     

    Maybe these can get you on track.

    - http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=49&messageid=147850

    - http://www.sqlservercentral.com/columnists/awarren/introductiontoadopart2recordsets.asp 

    - these are nice ones : http://www.sql-server-performance.com/q&a28.asp

       - http://www.sql-server-performance.com/cursors.asp

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution πŸ˜€

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

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

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