cursor replacement

Hello

I am trying follwoing code to repalace cursor, but it is giving spurious result

any suggestion/comment is welcome

Thanks
cursor.txt
jeet_78Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

bretCommented:
while exists( select * from #test where status='Y' )
begin
    set rowcount 1
   
    select @name = name,
              @key1=key1,
              @key2=key2,
              @key3=key3,
              @key4=key4
    from #test
   
    update #test
    set status='N'
   
    set rowcount 0
           
print "name: %1! , key1 : %2! , key2 : %3!, key3 : %4!, key4 : %5! ",@name,@key1,@key2,@key3,@key4    

end
-----------------

When you assign the results of a select to a variable, it is assigned the value of the
last row returned.   Your code doesn't do anything to assure that the UPDATE
is affecting the same row that was assigned to the variables.

Try changing the UPDATE to:
   update #test
    set status='N'  
    where name = @name
        and key1 = @key1
        and key2 = @key2
        and key3 = @key3
        and key4 = @key4
   

But I don't think even that is really good enough - I think you need to include indid and uid as well to form a good primary key that restricts this to one row.

This also isn't a good way to replace a cursor - you are still doing row by row processing rather than thinking about processing in terms of sets of rows.

I recommend you get Celko's new book "Thinking in Sets"  ISBN 0123741378 in particular, and his other books in general.
 
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jeet_78Author Commented:
Thank you
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Sybase Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.