cursor replacement


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

any suggestion/comment is welcome

Who is Participating?
bretConnect With a Mentor Commented:
while exists( select * from #test where status='Y' )
    set rowcount 1
    select @name = name,
    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    


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.
jeet_78Author Commented:
Thank you
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.

All Courses

From novice to tech pro — start learning today.