<p>Imagine the scene, you're updating some legacy Sybase code and come across a cursor. The stored procedure builds up a result set in a #temporary table which is all ready to be returned except that one of columns isn't terribly human readable, it's an alphanumeric code.</p>

<p>What we need to do, is figure out the possible distinct values of this code, call another stored procedure to cross reference these discrete values and then update the result set with the newly deciphered values:</p>

<pre><code>declare c_lookup_codes for
select distinct lookup_code
from #workinprogress

while(1=1)
begin
    fetch c_lookup_codes into @lookup_code

    if @@sqlstatus&lt;&gt;0
    begin
        break
    end

    exec proc_code_xref @lookup_code @xref_code OUTPUT

    update #workinprogress
    set xref = @xref_code
    where lookup_code = @lookup_code
end
</code></pre>

<p>Now then, whilst this may give some folks palpitations, it does work. My question is, how best would one avoid this kind of thing?</p>

<p>_NB: for the purposes of this example you can also imagine that the result set is in the region of 500k rows and that there are 100 distinct values of look_up_code and finally, that it is not possible to have a table with the xref values in as the logic in proc_code_xref is too arcane._</p>

[{'answer_id': 14671, 'author': 'Stu', 'author_id': 414, 'author_profile': 'https://Stackoverflow.com/users/414', 'pm_score': 1, 'selected': False, 'text': '<p>Unless you are willing to duplicate the code in the xref proc, there is no way to avoid using a cursor.</p>\n'}, {'answer_id': 14699, 'author': 'David Hayes', 'author_id': 1769, 'author_profile': 'https://Stackoverflow.com/users/1769', 'pm_score': 2, 'selected': True, 'text': "<p>You have to have a XRef table if you want to take out the cursor. Assuming you know the 100 distinct lookup values (and that they're static) it's simple to generate one by calling proc_code_xref 100 times and inserting the results into a table</p>\n"}, {'answer_id': 988077, 'author': 'B0rG', 'author_id': 122093, 'author_profile': 'https://Stackoverflow.com/users/122093', 'pm_score': 0, 'selected': False, 'text': "<p>They say, that if you must use cursor, then, you must have done something wrong ;-) here's solution without cursor:</p>\n\n<pre><code>declare @lookup_code char(8)\n\nselect distinct lookup_code\ninto #lookup_codes\nfrom #workinprogress\n\nwhile 1=1\nbegin\n    select @lookup_code = lookup_code from #lookup_codes\n\n    if @@rowcount = 0 break\n\n    exec proc_code_xref @lookup_code @xref_code OUTPUT\n\n    delete #lookup_codes\n    where lookup_code = @lookup_code\nend\n</code></pre>\n"}]
['https://Stackoverflow.com/questions/14577', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/1030/']