T-SQL 조건부 순서 기준

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값을 반환하는 식입니다.그것은 흐름 제어를 위한 것이 아닙니다, 예를 들어.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

사례 설명이 필요하지만 여러 사례 설명을 사용합니다.

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

네 개의 다른 절이 있으면 유형 간 변환 문제가 제거됩니다.

이 작업에는 여러 가지 방법이 있습니다.한 가지 방법은 다음과 같습니다.

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을 사용하여 이 작업을 수행할 수도 있습니다.

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

언급URL : https://stackoverflow.com/questions/15621609/t-sql-conditional-order-by