Oracle to SQL Server : dynamic query with limited rowset returned

Question

I have a difficult (if not impossible) problem to ask a solution for. I have an Oracle query, and I have to translate it to T-SQL syntax.

The query looks like this in Oracle:

__select_wrapper=select * from(
     select rownum row_num, inner__c3p__query.* 
     from ({0}) inner__c3p__query) 
     where row_num>@offset and (row_num<=(@offset+@count) or (@count<1))

This comes from a properties file. Basically, this one line executes any SQL query, names the resultset "inner__c3p__query", and selects everything from it and also a rownum. Then, it filters the result by the rownum: >@offset and <=@offset + @count. So, practically, it executes any SQL query, and returns a limited rowset, e.g. from the 10th row to the 20th.

Now, my job is to transform this into T-SQL. I spent one and a half day looking up every possible idea, but so far, I haven't succeeded. I tried a great many things, neither of them worked. The problem is, I can't use ROW_NUMBER() function, because it requires at least one column in the OVER() clause, and I can't provide any, because the actual query is only determined at runtime.

Do you guys have any idea, or is it really impossible to do on the DB side?

Thanks in advance!

Gabe

Answer 1

While I have a lot of reservations about this kind of approach, especially wrt SQL Injection attacks, the following should work:

select * from(
 select ROW_NUMBER() OVER(ORDER BY (Select NULL)) row_num, 
    inner__c3p__query.* 
 from ({0}) inner__c3p__query) 
 where row_num>@offset and (row_num<=(@offset+@count) or (@count<1))

Unfortunately, since the ordering column never changes, if you are trying to use this for paging, you cannot guarantee that the same row_num is always assigned to the same row in repeated executions.