Search code examples
c#sqloracle-databaseplsql

Is it possible to have conditional 'FETCH NEXT X ROWS ONLY' by checking the result set count

I'm using oracle client in .net framework (c#) and need to limit the number of records returned in case where number of records are bigger then X

When constructing sql I understand I can append FETCH NEXT X ROWS ONLY if I first execute the query to get the count against the sql I'm constructing and if count exceeds X I can append FETCH NEXT X ROWS ONLY statement.

I'm curious is it possible to achieve that in one shot rather then getting the count and appending fetch next if count exceeds x

Solution

  • As I said in the comment, if you never display more than 300 rows, then limit to 301:

    select *
from t
fetch next 301 rows only

    Upon receiving the result, there are two cases:

    • If you get 300 rows or less, you just display them, and no warning is necessary.
    • If you get 301 rows you display the first 300 (ignoring the last one), this time displaying the warning. Easy, isn't it?

    This is pretty much a standard solution for this case.