Incorrect syntax near '@SortOrder'. Invalid usage of the option NEXT in the FETCH statement

Question

I'm using Dapper 2.0.35, MS SQL Server 14.0.1. I'm getting SQL Exception when I run the following code:

string sortField = "Id";
switch (sortBy)
{
    case "city":
        sortField = "City";
        break;
    case "state":
        sortField = "State";
        break;
    case "county":
        sortField = "County";
        break;
    case "country":
        sortField = "Country";
        break;
    default:
        break;
}

// Get all customers based on the filter criteria
string sortOrder = sortDesc == true ? "DESC" : "ASC";
int offsetRows = pageSize * (pageNumber - 1);
var parameters = new
{
    SortField = sortField,
    SortOrder = sortOrder,
    OffsetRows = offsetRows,
    PageSize = pageSize,
};

// Execute!
var sql = "SELECT * FROM Customer ORDER BY @SortField @SortOrder OFFSET @OffsetRows ROWS FETCH NEXT @PageSize ROWS ONLY";
var result = (await connection.QueryAsync<Customer>(sql, parameters)).AsList();

It seems to me that that is a valid SQL query. Exception said incorrect syntax near @SortOrder, but where did I do wrong? I tested the following query (which is similar to my code?) in SQL, ran just fine:

SELECT * FROM Customer ORDER BY County ASC OFFSET 0 ROWS FETCH NEXT 25 ROWS ONLY

Where did I do wrong?

You can't parameterize ordering field and order direction. You have literally written `ORDER BY value1 value2`. — Lasse V. Karlsen, Oct 07 '20 at 15:25
@LasseV.Karlsen gave me the correct answer. Yeah, that was why. So I'm supposed to write my query like this? $"SELECT yadayada {sortField} {sortOrder} yadayada" — Farid, Oct 07 '20 at 15:29

score 0 · Accepted Answer · answered Oct 07 '20 at 15:30

Most SQL engines will not allow you parameterize ordering fields and direction as stated in the comments. The work around to this in general is to use a CASE statement to on the parameter.

CASE @SortField
    When 'city' then City
    When 'state' then State
End

Something like the above should work with SQL. You can take a look at this older StackOverflow question for more: "Order By" using a parameter for the column name

Incorrect syntax near '@SortOrder'. Invalid usage of the option NEXT in the FETCH statement

1 Answers1