Solved

cursor replacement

Posted on 2008-10-21
2
413 Views
Last Modified: 2012-05-05
Hello

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

any suggestion/comment is welcome

Thanks
cursor.txt
0
Comment
Question by:jeet_78
2 Comments
 
LVL 10

Accepted Solution

by:
bret earned 500 total points
Comment Utility
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
 

Author Closing Comment

by:jeet_78
Comment Utility
Thank you
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Restoring deleted objects in Active Directory has been a standard feature in Active Directory for many years, yet some admins may not know what is available.
This article explains how to prepare an HTML email signature template file containing dynamic placeholders for users' Azure AD data. Furthermore, it explains how to use this file to remotely set up a department-wide email signature policy in Office …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now