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: 135368 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

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.