Search code examples
oraclesql-server-2012cursor

SQL Server equivalent for Oracle CURRVAL from dual

What is the purpose of these Oracle block ? Especially the keyword CURRVAL and ROWNUM . How do I convert these statements into SQL Server 2012?

CURSOR cursor_1 IS
        SELECT ID.CURRVAL identity FROM dual;

CURSOR cursor_2 IS
        SELECT COUNT(*) Count_total  FROM tbl1
         WHERE ROWNUM = 1;
Solution

  • CURRVAL gets the current value (without increasing it) of an Oracle sequence. (NEXTVAL increases the sequence and retrieves the new current value.) See documentation here:

    http://docs.oracle.com/database/121/SQLRF/pseudocolumns002.htm#SQLRF00253

    Your cursor_1 is simply retrieving the current value of a sequence named "ID".

    SQL Server I believe does not have sequences, but I am not sure if Microsoft might have added something like it in newer releases? SQL Server uses identity columns and not sequences. The cursor cannot be converted to SQL Server in a meaningful fashion. You would have to know what is the purpose of the cursor? Probably it would have to be replaced by something like selecting the value of the identity column of the newly inserted row (if this is happening just after inserting into a table with a trigger that uses ID.NEXTVAL for example.)

    --

    WHERE ROWNUM = 1 just selects one "random" row from tbl1 and counts it. So count_total will be either 0 or 1 depending on whether rows exist in tbl1 or not. Perhaps that can be replaced with a SELECT TOP 1, but I am not sure if a COUNT in a TOP query counts after the TOP has been processed or before the TOP.