I''d like to have this anaylyzed.

Question

Post reply

I''d like to have this anaylyzed.

dB-226266

Ten Centuries

Points: 1298
More actions
April 19, 2005 at 9:15 pm

#164238

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

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

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

Johan Bijnens SSC Guru Points: 134873 More actions · Answer 1

- 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

Mike Metcalf SSCrazy Eights Points: 8763 More actions · Answer 2

Mike Metcalf

SSCrazy Eights

Points: 8763

April 21, 2005 at 7:26 am

#554070

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.