Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

cursor replacement

Posted on 2008-10-21
2
Medium Priority
?
454 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 10

Accepted Solution

by:
bret earned 1500 total points
ID: 22768440
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
ID: 31508308
Thank you
0

Featured Post

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article, I’ll show how research, determination, and use of modern technology helped me solve a DNA mystery.
By default Outlook 2016 displays only one time zone in the Calendar. The following article explains how to display two time zones in one calendar view.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

721 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