Подтвердить что ты не робот

"Order By" с использованием параметра для имени столбца

Мы хотели бы использовать параметр в предложении Order by для запроса или хранимой процедуры, созданной с помощью Visual Studio DataSet Designer.

Пример:

  FROM TableName
 WHERE (Forename LIKE '%' + @SearchValue + '%') OR
       (Surname LIKE '%' + @SearchValue + '%') OR
       (@SearchValue = 'ALL')
ORDER BY @OrderByColumn

Эта ошибка отображается:

Variables are only allowed when ordering by an expression referencing 
a column name.

Ответ 1

Вы должны сделать что-то вроде этого:

SELECT *
FROM
    TableName
WHERE
    (Forename LIKE '%' + @SearchValue + '%') OR
    (Surname LIKE '%' + @SearchValue + '%') OR
    (@SearchValue = 'ALL')
ORDER BY 
    CASE @OrderByColumn
    WHEN 1 THEN Forename
    WHEN 2 THEN Surname
    END;

Назначьте от 1 до @OrderByColumn для сортировки по Forename.
Назначьте 2 для сортировки по Surname.
Etc... вы можете развернуть эту схему на произвольное количество столбцов.

Будьте осторожны с производительностью. Эти типы конструкций могут препятствовать возможности оптимизатора запросов для поиска оптимального плана выполнения. Например, даже если Forename покрывается индексом, запрос может по-прежнему нуждаться в полной сортировке, а не просто перемещать индекс по порядку.

Если это так, и вы не можете жить с последствиями производительности, может потребоваться отдельная версия запроса для каждого возможного порядка сортировки, что усложняет ситуацию на стороне клиента.

Ответ 2

Я знаю, что я захожу в эту ветку слишком поздно, но я просто хочу опубликовать это на тот случай, если у кого-то еще возникнут подобные проблемы.

Эта проблема возникает, когда вы пытаетесь выполнить ORDER BY непосредственно для параметра, поскольку SQL Server ожидает, что вы предоставите число (1 для первого поля, 2 для второго и т.д.) Или столбец имя представлено в виде идентификатора (MyField или "MyField") или строки ("MyField").

Например:

DECLARE @ORDERBY AS NVARCHAR(20)
;

SELECT @ORDERBY = :Param1 --(Supposing that the user enters 'MyField')
;

SELECT TOP 1 *
FROM MyTable
ORDER BY @ORDERBY DESC
;

Вы получаете следующую ошибку:

Элемент SELECT, идентифицируемый номером 1 ORDER BY, содержит переменную как часть выражения, определяющую положение столбца. Переменные допускаются только при упорядочении по выражению, ссылающемуся на имя столбца. (SQLSTATE = 42000) (1008) (серьезность = 16)

Если вы выписываете запрос вручную любым из описанных способов (с использованием идентификатора или строки), ошибки не будет.

SELECT TOP 1 *
FROM MyTable
ORDER BY MyField DESC
;

SELECT TOP 1 *
FROM MyTable
ORDER BY "MyField" DESC
;

SELECT TOP 1 *
FROM MyTable
ORDER BY 'MyField' DESC
;

Поэтому, если вы выполняете CAST() для этого же параметра, его значение преобразуется в строку, и запрос выполняется успешно:

DECLARE @ORDERBY AS NVARCHAR(20)
;

SELECT @ORDERBY = :Param1 --(Supposing that the user enters the text 'MyField')
;

SELECT TOP 1 *
FROM MyTable
ORDER BY CAST(@ORDERBY AS NVARCHAR(20)) DESC
;

В этом случае (опять же, предположим, что пользователь записал строку "MyField" в качестве значения: Param1), фактический выполняемый запрос:

SELECT TOP 1 *
FROM MyTable
ORDER BY 'MyField' DESC
;

Этот запрос выполняется успешно, без ошибок и без видимого значительного влияния на производительность, без необходимости перечислять все возможные пользовательские входные данные в оператор CASE который может потенциально охватывать сотни возможных значений.

Я использовал это решение много раз в Microsoft SQL Server, с 2005 по 2016 год, без каких-либо проблем.

Надеюсь, это все еще может быть полезным для кого-то.