Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 456
  • Last Modified:

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
0
jeet_78
Asked:
jeet_78
1 Solution
 
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
 
jeet_78Author Commented:
Thank you
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now