[sql] SQL 저장 프로 시저 내 동적 정렬

이것은 과거에 조사하는 데 몇 시간을 소비 한 문제입니다. 그것은 현대의 RDBMS 솔루션 으로 해결해야 할 것으로 보이지만 아직 데이터베이스 백엔드가있는 웹 또는 Windows 응용 프로그램에서 매우 일반적으로 필요한 것으로 보이는 것을 발견하지 못했습니다.

동적 정렬에 대해 말합니다. 내 환상의 세계에서는 다음과 같이 간단해야합니다.

ORDER BY @sortCol1, @sortCol2

이것은 인터넷상의 포럼에서 초보자 SQL 및 저장 프로 시저 개발자가 제공 한 정식 예제 입니다. “왜 이것이 불가능합니까?” 그들이 묻다. 항상 누군가 저장 프로 시저의 컴파일 된 특성, 일반적으로 실행 계획, 그리고 매개 변수를 ORDER BY절에 직접 넣을 수없는 다른 모든 이유에 대해 강의하기 위해 누군가를 따라옵니다 .

“고객이 정렬을하도록하세요.” 당연히 이것은 데이터베이스에서 작업을 오프로드합니다. 우리의 경우 데이터베이스 서버는 99 %의 땀을 흘리지 않으며 6 개월마다 발생하는 시스템 아키텍처에 대한 멀티 코어 나 다른 개선 사항도 없습니다. 이러한 이유만으로도 데이터베이스에서 정렬을 처리하는 것은 문제가되지 않습니다. 또한 데이터베이스는 매우정렬에 좋습니다. 그것들은 그것을 위해 최적화되어 있고 그것을 올바르게하기 위해 수년이 걸렸으며, 그것을하기위한 언어는 매우 유연하고, 직관적이며, 단순합니다. 무엇보다도 초보자 SQL 작성자는 어떻게해야하는지 알고, 더 중요하게는 그것을 편집하는 방법을 알고 있습니다. 데이터베이스에 세금이 부과되지 않고 개발 시간을 단순화하고 단축하려는 경우 이는 분명한 선택으로 보입니다.

그런 다음 웹 문제가 있습니다. 나는 HTML 테이블의 정렬 클라이언트 측을 자바 스크립트로 주위를 해본 적이 있지만, 그들은 필연적으로 내 요구에 대한 유연한 부족하고, 다시 내 데이터베이스가 과도하게 과세되지 않기 때문에 정말 정렬 할 수있는 정말 쉽게 I JavaScript 정렬기를 다시 작성하거나 롤업하는 데 걸리는 시간을 정당화하는 데 어려움을 겪고 있습니다. 일반적으로 서버 측 정렬에도 동일하지만 JavaScript보다 이미 훨씬 선호됩니다. 나는 특히 DataSets의 오버 헤드를 좋아하는 사람이 아니므로 고소하십시오.

그러나 이것은 불가능하지 않다는 점을 되 찾는다. 나는 이전 시스템에서 동적 정렬을 얻는 엄청나게 해킹 방법을 수행했습니다. 예쁘지 않고 직관적이거나 단순하거나 유연하지 않았으며 초급 SQL 작성기가 몇 초 안에 사라질 것입니다. 이미 이것은 “솔루션”이 아니라 “합병증”인 것으로 보입니다.

다음 예제는 모범 사례 나 우수한 코딩 스타일 등을 나타내거나 T-SQL 프로그래머로서의 내 능력을 나타내는 것이 아닙니다. 그것들은 그들이 무엇이며, 나는 그들이 혼란스럽고 나쁜 형태이며 단지 평범한 해킹임을 완전히 인정합니다.

우리는 정수 값을 저장 프로 시저에 매개 변수로 전달하고 (매개 변수를 “정렬”이라고 함) 그로부터 여러 변수를 결정합니다. 예를 들어 … sort가 1 (또는 기본값)이라고 가정 해 보겠습니다.

DECLARE @sortCol1 AS varchar(20)
DECLARE @sortCol2 AS varchar(20)
DECLARE @dir1 AS varchar(20)
DECLARE @dir2 AS varchar(20)
DECLARE @col1 AS varchar(20)
DECLARE @col2 AS varchar(20)

SET @col1 = 'storagedatetime';
SET @col2 = 'vehicleid';

IF @sort = 1                -- Default sort.
BEGIN
    SET @sortCol1 = @col1;
    SET @dir1 = 'asc';
    SET @sortCol2 = @col2;
    SET @dir2 = 'asc';
END
ELSE IF @sort = 2           -- Reversed order default sort.
BEGIN
    SET @sortCol1 = @col1;
    SET @dir1 = 'desc';
    SET @sortCol2 = @col2;
    SET @dir2 = 'desc';
END

다른 열을 정의하기 위해 더 많은 @colX 변수를 선언 한 경우 “정렬”값을 기준으로 정렬 할 열로 창의적으로 만들 수있는 방법을 이미 알 수 있습니다. 사용하려면 보통 다음과 같이 보입니다. 엄청나게 지저분한 조항 :

ORDER BY
    CASE @dir1
        WHEN 'desc' THEN
            CASE @sortCol1
                WHEN @col1 THEN [storagedatetime]
                WHEN @col2 THEN [vehicleid]
            END
    END DESC,
    CASE @dir1
        WHEN 'asc' THEN
            CASE @sortCol1
                WHEN @col1 THEN [storagedatetime]
                WHEN @col2 THEN [vehicleid]
            END
    END,
    CASE @dir2
        WHEN 'desc' THEN
            CASE @sortCol2
                WHEN @col1 THEN [storagedatetime]
                WHEN @col2 THEN [vehicleid]
            END
    END DESC,
    CASE @dir2
        WHEN 'asc' THEN
            CASE @sortCol2
                WHEN @col1 THEN [storagedatetime]
                WHEN @col2 THEN [vehicleid]
            END
    END

분명히 이것은 매우 벗겨진 예입니다. 실제 항목은 일반적으로 정렬을 지원하기 위해 4 개 또는 5 개의 열이 있기 때문에 각 열에는 정렬 할 수있는 보조 열 또는 세 번째 열 (예 : 내림차순, 이름 오름차순으로 두 번째로 정렬 된 날짜) 및 각 지원 Bi- 케이스의 수를 효과적으로 두 배로하는 방향성 정렬. 응 .. 정말 빨리 털이 나옵니다.

아이디어는 저장 날짜 이전에 vehicleid가 정렬되도록 정렬 사례를 “쉽게”변경할 수 있다는 것입니다. 그러나이 간단한 예제에서 의사 유연성은 실제로 거기서 끝납니다. 본질적으로 테스트에 실패한 각 경우 (이번에는 정렬 방법이 적용되지 않기 때문에) NULL 값을 렌더링합니다. 따라서 다음과 같은 기능을하는 절로 끝납니다.

ORDER BY NULL DESC, NULL, [storagedatetime] DESC, blah blah

당신은 아이디어를 얻습니다. SQL Server는 null 값을 순서대로 효과적으로 무시하기 때문에 작동합니다. SQL에 대한 기본 작업 지식이있는 사람이라면 누구나 볼 수 있듯이 유지 관리가 매우 어렵습니다. 내가 당신을 잃어버린 경우에도 나쁘게 느끼지 마십시오. 작동하는 데 오랜 시간이 걸렸으며 여전히 편집하거나 새로운 것을 만드는 데 혼란을 겪습니다. 고맙게도 자주 변경하지 않아도됩니다. 그렇지 않으면 신속하게 “문제의 가치가 없습니다”.

그러나 그것은 한 작품.

내 질문은 다음과 같습니다. 더 좋은 방법이 있습니까?

저장 프로 시저 솔루션 이외의 솔루션은 괜찮습니다. 가급적이면 누구나 저장 프로 시저 내에서 더 잘 할 수 있는지 알고 싶지만 그렇지 않은 경우 ASP.NET을 사용하여 사용자가 데이터 테이블을 양방향으로 동적으로 정렬 할 수 있도록 어떻게 처리합니까?

그리고 긴 질문을 읽어 주셔서 감사합니다.

추신 : 동적 정렬, 동적 필터링 / 열의 텍스트 검색, ROWNUMBER () OVER를 통한 페이지 매김 및 오류에 대한 트랜잭션 롤백이있는 캐치 시도 를 지원하는 저장 프로 시저의 예를 보여주지 않았다는 것을 기쁘게 생각 합니다 … “behemoth-sized”는 심지어 그것들을 묘사하기 시작하지도 않습니다.

최신 정보:

• 동적 SQL 을 피하고 싶습니다 . 문자열을 함께 구문 분석하고 EXEC를 실행하면 저장 프로 시저를 처음부터 사용하려는 많은 목적을 잃게됩니다. 때로는 이러한 특수한 동적 정렬 사례에서 그러한 일을하는 데 가치가 없는지 궁금합니다. 아직도, 나는 여전히 고전적인 ASP 세계에 살고있는 것처럼 동적 SQL 문자열을 수행 할 때마다 항상 더럽습니다.
• 우리가 처음에 저장 프로 시저를 원하는 많은 이유는 보안을 위한 것 입니다. 보안 문제에 대해서는 전화를 걸지 말고 솔루션 만 제안하십시오. SQL Server 2005를 사용하면 개별 저장 프로 시저의 스키마 수준에서 필요한 경우 사용자별로 권한을 설정 한 다음 테이블에 대한 쿼리를 직접 거부 할 수 있습니다. 이 접근법의 장단점을 결정하는 것은 아마도 또 다른 질문 일 것입니다.하지만 다시 한 번 내 결정이 아닙니다. 나는 단지 리드 코드 원숭이입니다. 🙂

답변

order by
case when @SortExpr = 'CustomerName' and @SortDir = 'ASC'
    then CustomerName end asc,
case when @SortExpr = 'CustomerName' and @SortDir = 'DESC'
    then CustomerName end desc,
...

SELECT
  s.*
FROM
  (SELECT
    CASE @SortCol1
      WHEN 'Foo' THEN t.Foo
      WHEN 'Bar' THEN t.Bar
      ELSE null
    END as SortCol1,
    CASE @SortCol2
      WHEN 'Foo' THEN t.Foo
      WHEN 'Bar' THEN t.Bar
      ELSE null
    END as SortCol2,
    t.*
  FROM
    MyTable t) as s
ORDER BY
  CASE WHEN @dir1 = 'ASC'  THEN SortCol1 END ASC,
  CASE WHEN @dir1 = 'DESC' THEN SortCol1 END DESC,
  CASE WHEN @dir2 = 'ASC'  THEN SortCol2 END ASC,
  CASE WHEN @dir2 = 'DESC' THEN SortCol2 END DESC

SELECT
   name_last,
   name_first,
   CASE @sortCol WHEN 'name_last' THEN [name_last] ELSE 0 END as mySort
FROM
   table
ORDER BY
    mySort

create table #temp ( your columns )

insert #temp
exec foobar

select * from #temp order by whatever

create procedure uspCallAndSort
(
    @sql varchar(2048),        --exec dbo.uspSomeProcedure arg1,'arg2',etc.
    @sortClause varchar(512)    --comma-delimited field list
)
AS
insert into #tmp EXEC(@sql)
declare @msql varchar(3000)
set @msql = 'select * from #tmp order by ' + @sortClause
EXEC(@msql)
drop table #tmp
GO