Andrew Channels Dexter Pinion

Wherein I write some stuff that you may like to read. Or not, its up to you really.

December 20, 2002

Oracle v SQL Server, Part 5

The hacking continues. The very last thing I have to convert from Oracle to SQL Server is a script which performs some conditional processing on my data.

In Oracle I wrote a fairly simple script in PL/SQL which opened in turn and nested three cursors. The nesting is important here, as values from the outer cursor results sets were passed to the inner cursors when they were opened as cursor parameters. Simple, and fairly routine in PL/SQL.

Taking this and converting it to Transact SQL I found a problem. There didn't seem to be any way of saying "open cursor b with this value I have just retrieved from cursor a". Well, you can, but it cannot be repeated. The problem, it seems, is one of scope.

PL/SQL follows the standard convention of most programming languages in that code units are comprised of a declaration section, followed by an execution section (encloded by some kind of markers, e.g. { } or BEGIN END). Transact SQL doesn't do this. So instead of;

    cursor a ...
    cursor b ...
    open a
    fetch a row from a
      open b with values from current value of a
    end loop

We have to do something like;

  open a
  fetch a value from a
  DECLARE b using the value from a
    fetch a row from b
  end loop

Which took a little bit of getting used to. Still, plus ca change.

Posted by Andy Todd at December 20, 2002 03:10 PM