0

I am trying to sent a query string to the database, but got this error -->

A TOP can not be used in the same query or sub-query as a OFFSET.

When I debugged the query string , I observe that "select * from TABLE" is changed to "select * from TABLE Fetch Next 50 Rows Only"(where number of columns =50(already set)).

Required Output

select top * x from [TABLE]

to

select * from [TABLE] Fetch Next x Rows Only

What I tried

I tried to use , find and replace the 'top * x' to integer x and *

 string CommandText = "select * from TABLE";
 CommandText =CommandText
 + orderby + " OFFSET " + (request.Page - 1) * request.PageSize
 + " ROWS FETCH NEXT " + request.PageSize + " ROWS ONLY";

Kindly help me to find the solution.

Cleptus
  • 3,446
  • 4
  • 28
  • 34
Sarath Mohandas
  • 472
  • 1
  • 9
  • 25
  • 3
    I am unclear as to what you are trying to do. Firstly, building a SQL Query in this way is dangers and could result in SQL injection. Secondly, the FETCH keyword I believe only works with cursors so it wouldn't work in this scenario. Please clarify what it is you are trying to accomplish and I may be able to help. – Brandon Johnson Nov 06 '20 at 12:57
  • 1
    Why are you trying to add a `TOP` over a SQL statement that already retrieves the amount of rows you want? – Cleptus Nov 06 '20 at 12:57
  • @Cleptus since the number data required for grid is fixed in the range 10,20,30,40 etc instead of it i need user defined range. – Sarath Mohandas Nov 06 '20 at 13:04
  • It is redundant, you already achieve it with the `FETCH NEXT x ROWS ONLY`, check my answer – Cleptus Nov 06 '20 at 13:06
  • 1
    @BrandonJohnson In SQL Server it is valid using FETCH in an SELECT statement – Cleptus Nov 06 '20 at 13:07
  • 3
    You should **absolutely never** run arbitrary SQL from external sources. Never. That is a security hole that you could drive a truck through. – Daniel Mann Nov 06 '20 at 13:08
  • Thanks @Cleptus I just have never seen it done. This is why I'm not a DBA lol. – Brandon Johnson Nov 06 '20 at 13:13
  • @DanielMann changed my answer to have more enphasis on why OP should use parametrized queries – Cleptus Nov 06 '20 at 13:13
  • 1
    @Cleptus A parameterized query won't solve the problem. If this page in question is user-facing, a savvy user will notice the SQL and realize they can write any SQL they want. And then you have a security hole. – Daniel Mann Nov 06 '20 at 13:15
  • 1
    @DanielMann No, the database engine does use the value received as is, with no interpreting it any further. Check [Can parameterized statement stop all SQL injection?](https://stackoverflow.com/questions/6786034/can-parameterized-statement-stop-all-sql-injection) – Cleptus Nov 06 '20 at 13:21

1 Answers1

4

In order to avoid string concatenation to make the SQL statement you could (and IMHO you should) use a parametrized query. It is a security risk called SQL Injection you should always avoid. For example:

string commandText = @"
SELECT * FROM dbo.TABLE
ORDER BY orderColum
OFFSET @offset ROWS
FETCH NEXT @page ROWS ONLY";

command.CommandText = commandText;
command.Parameters.Add(SqlDbType.Int, "@offset").Value =  (request.Page - 1) * request.PageSize;
command.Parameters.Add(SqlDbType.Int, "@page").Value =  request.PageSize;

Please do note that parametrized queries do have a bonus, the database engine will likely do an execution plan for it and future queries using the same CommandText would execute faster because of that plan regarless of having different values in their parameters.

Cleptus
  • 3,446
  • 4
  • 28
  • 34