Условный порядок T-SQL

Я пытаюсь написать хранимую процедуру, которая возвращает список объектов с порядком сортировки и направлением сортировки, выбранным пользователем, и передается как параметры sql.

Предположим, что у меня есть таблица продуктов со следующими столбцами: product_id (int), name (varchar), value (int), created_date (datetime) и параметры @sortDir и @sortOrder

Я хочу сделать что-то вроде

select *
from Product
  if (@sortOrder = 'name' and @sortDir = 'asc') 
  then order by name asc
  if (@sortOrder = 'created_date' and @sortDir = 'asc') 
  then order by created_date asc
  if (@sortOrder = 'name' and @sortDir = 'desc') 
  then order by name desc
  if (@sortOrder = 'created_date' and @sortDir = 'desc') 
  then order by created_date desc

Я попытался сделать это с аргументами case, но возникли проблемы, так как типы данных были разными. У кого-нибудь есть предложения?

Ответ 1

CASE - это выражение, которое возвращает значение. Это не для управления потоком, как IF. И вы не можете использовать IF в запросе.

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

SELECT column_list_please
FROM dbo.Product -- dbo prefix please
ORDER BY 
  CASE WHEN @sortDir = 'asc' AND @sortOrder = 'name' THEN name END,
  CASE WHEN @sortDir = 'asc' AND @sortOrder = 'created_date' THEN created_date END,
  CASE WHEN @sortDir = 'desc' AND @sortOrder = 'name' THEN name END DESC,
  CASE WHEN @sortDir = 'desc' AND @sortOrder = 'created_date' THEN created_date END DESC;

Возможно, более простое решение (особенно если это усложняется) заключается в использовании динамического SQL. Чтобы помешать SQL-инъекции, вы можете проверить значения:

IF @sortDir NOT IN ('asc', 'desc')
  OR @sortOrder NOT IN ('name', 'created_date')
BEGIN
  RAISERROR('Invalid params', 11, 1);
  RETURN;
END

DECLARE @sql NVARCHAR(MAX) = N'SELECT column_list_please
  FROM dbo.Product ORDER BY ' + @sortOrder + ' ' + @sortDir;

EXEC sp_executesql @sql;

Еще один плюс для динамического SQL, несмотря на все распространенные опасения: вы можете получить лучший план для каждого варианта сортировки, а не один план, который будет оптимизирован для любого варианта сортировки, который вы использовали первый. Он также наилучшим образом реализовывался в недавнем сравнении производительности, которое я выполнил:

http://sqlperformance.com/conditional-order-by

Ответ 2

Вам нужен оператор case, хотя я бы использовал несколько операторов case:

order by (case when @sortOrder = 'name' and @sortDir = 'asc' then name end)  asc,
         (case when @sortOrder = 'name' and @sortDir = 'desc' then name end) desc,
         (case when @sortOrder = 'created_date' and @sortDir = 'asc' then created_date end) asc,
         (case when @sortOrder = 'created_date' and @sortDir = 'desc' then created_date end) desc

Наличие четырех различных предложений устраняет проблему преобразования типов.

Ответ 3

Существует несколько способов сделать это. Один из способов:

SELECT *
FROM
(
  SELECT
  ROW_NUMBER() OVER ( ORDER BY
  CASE WHEN @sortOrder = 'name' and @sortDir = 'asc' then name
  END ASC,
  CASE WHEN @sortOrder = 'name' and @sortDir = 'desc' THEN name
  END DESC,
  CASE WHEN i(@sortOrder = 'created_date' and @sortDir = 'asc' THEN created_date
  END ASC,
  CASE WHEN i(@sortOrder = 'created_date' and @sortDir = 'desc' THEN created_date
  END ASC) RowNum
  *
)
order by 
RowNum

Вы также можете сделать это с помощью динамического sql.

Ответ 4

declare @str varchar(max)
set @str = 'select * from Product order by ' + @sortOrder + ' ' + @sortDir
exec(@str)

Ответ 5

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

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

DECLARE @RawProducts TABLE (
    product_id int, 
    name varchar(50), 
    value int, 
    created_date datetime
)
INSERT INTO @RawProducts
SELECT * FROM [Product]

IF @sortOrder = 'name' AND @sortDir = 'asc' BEGIN
    SELECT * FROM @RawProducts
    ORDER BY [name] ASC
END

IF @sortOrder = 'name' AND @sortDir = 'desc' BEGIN
    SELECT * FROM @RawProducts
    ORDER BY [name] desc
END

IF @sortOrder = 'created_date' AND @sortDir = 'asc' BEGIN
    SELECT * FROM @RawProducts
    ORDER BY [created_date] ASC
END

IF @sortOrder = 'created_date' AND @sortDir = 'desc' BEGIN
    SELECT * FROM @RawProducts
    ORDER BY [created_date] desc
END

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

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