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,
...

나에게 이것은 코드에서 동적 SQL을 작성하는 것보다 훨씬 낫습니다. 이로 인해 DBA의 확장 성 및 유지 보수 악몽이됩니다.

What I do from code is refactor the paging and sorting so I at least don't have a lot of repetition there with populating values for @SortExpr and @SortDir.

As far as the SQL is concerned, keep the design and formatting the same between different stored procedures, so it's at least neat and recognizable when you go in to make changes.

---

This approach keeps the sortable columns from being duplicated twice in the order by, and is a little more readable IMO:

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

---

Dynamic SQL is still an option. You just have to decide whether that option is more palatable than what you currently have.

Here is an article that shows that: http://www.4guysfromrolla.com/webtech/010704-1.shtml.

---

My applications do this a lot but they are all dynamically building the SQL. However, when I deal with stored procedures I do this:

1. Make the stored procedure a function that returns a table of your values - no sort.
2. Then in your application code do a select * from dbo.fn_myData() where ... order by ... so you can dynamically specify the sort order there.

Then at least the dynamic part is in your application, but the database is still doing the heavy lifting.

---

There's a couple of different ways you can hack this in.

Prerequisites:

1. Only one SELECT statement in the sp
2. Leave out any sorting (or have a default)

Then insert into a temp table:

create table #temp ( your columns )

insert #temp
exec foobar

select * from #temp order by whatever

Method #2: set up a linked server back to itself, then select from this using openquery: http://www.sommarskog.se/share_data.html#OPENQUERY

---

There may be a third option, since your server has lots of spare cycles - use a helper procedure to do the sorting via a temporary table. Something like

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

Caveat: I haven't tested this, but it "should" work in SQL Server 2005 (which will create a temporary table from a result set without specifying the columns in advance.)

---

A stored procedure technique (hack?) I've used to avoid dynamic SQL for certain jobs is to have a unique sort column. I.e.,

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

This one is easy to beat into submission -- you can concat fields in your mySort column, reverse the order with math or date functions, etc.

Preferably though, I use my asp.net gridviews or other objects with build-in sorting to do the sorting for me AFTER retrieving the data fro Sql-Server. Or even if it's not built-in -- e.g., datatables, etc. in asp.net.

---

At some point, doesn't it become worth it to move away from stored procedures and just use parameterized queries to avoid this sort of hackery?

---

I agree, use client side. But it appears that is not the answer you want to hear.

So, it is perfect the way it is. I don't know why you would want to change it, or even ask "Is there a better way." Really, it should be called "The Way". Besides, it seems to work and suit the needs of the project just fine and will probably be extensible enough for years to come. Since your databases aren't taxed and sorting is really really easy it should stay that way for years to come.

I wouldn't sweat it.

---

When you are paging sorted results, dynamic SQL is a good option. If you're paranoid about SQL injection you can use the column numbers instead of the column name. I've done this before using negative values for descending. Something like this...

declare @o int;
set @o = -1;

declare @sql nvarchar(2000);
set @sql = N'select * from table order by ' + 
    cast(abs(@o) as varchar) + case when @o < 0 then ' desc' else ' asc' end + ';'

exec sp_executesql @sql

Then you just need to make sure the number is inside 1 to # of columns. You could even expand this to a list of column numbers and parse that into a table of ints using a function like this. Then you would build the order by clause like so...

declare @cols varchar(100);
set @cols = '1 -2 3 6';

declare @order_by varchar(200)

select @order_by = isnull(@order_by + ', ', '') + 
        cast(abs(number) as varchar) + 
        case when number < 0 then ' desc' else '' end
from dbo.iter_intlist_to_tbl(@cols) order by listpos

print @order_by

One drawback is you have to remember the order of each column on the client side. Especially, when you don't display all the columns or you display them in a different order. When the client wants to sort, you map the column names to the column order and generate the list of ints.

---

An argument against doing the sorting on the client side is large volume data and pagination. Once your row count gets beyond what you can easily display you're often sorting as part of a skip/take, which you probably want to run in SQL.

For Entity Framework, you could use a stored procedure to handle your text search. If you encounter the same sort issue, the solution I've seen is to use a stored proc for the search, returning only an id key set for the match. Next, re-query (with the sort) against the db using the ids in a list (contains). EF handles this pretty well, even when the ID set is pretty large. Yes, this is two round trips, but it allows you to always keep your sorting in the DB, which can be important in some situations, and prevents you from writing a boatload of logic in the stored procedure.

---

How about handling sorting on the stuff displaying the results -- grids, reports, etc. rather than on SQL?

EDIT:

To clarify things since this answer got down-voted earlier, I'll elaborate a bit...

You stated you knew about client-side sorting but wanted to steer clear of it. That's your call, of course.

What I want to point out, though, is that by doing it on the client-side, you're able to pull data ONCE and then work with it however you want -- versus doing multiple trips back and forth to the server each time the sort gets changed.

Your SQL Server isn't getting taxed right now and that's awesome. It shouldn't be. But just because it isn't overloaded yet doesn't mean that it'll stay like that forever.

If you're using any of the newer ASP.NET stuff for displaying on the web, a lot of that stuff is already baked right in.

Is it worth adding so much code to each stored procedure just to handle sorting? Again, your call.

I'm not the one who will ultimately be in charge of supporting it. But give some thought to what will be involved as columns are added/removed within the various datasets used by the stored procedures (requiring modifications to the CASE statements) or when suddenly instead of sorting by two columns, the user decides they need three -- requiring you to now update every one of your stored procedures that uses this method.

For me, it's worth it to get a working client-side solution and apply it to the handful of user-facing displays of data and be done with it. If a new column is added, it's already handled. If the user wants to sort by multiple columns, they can sort by two or twenty of them.

---

Sorry I'm late to the party, but here's another option for those who really want to avoid dynamic SQL, but want the flexibility it offers:

Instead of dynamically generating the SQL on the fly, write code to generate a unique proc for every possible variation. Then you can write a method in the code to look at the search options and have it choose the appropriate proc to call.

If you only have a few variations then you can just create the procs by hand. But if you have a lot of variations then instead of having to maintain them all, you would just maintain your proc generator instead to have it recreate them.

As an added benefit, you'll get better SQL plans for better performance doing it this way too.

---

This solution might only work in .NET, I don't know.

I fetch the data into the C# with the initial sort order in the SQL order by clause, put that data in a DataView, cache it in a Session variable, and use it to build a page.

When the user clicks on a column heading to sort (or page, or filter), I don't go back to the database. Instead, I go back to my cached DataView and set its "Sort" property to an expression I build dynamically, just like I would dynamic SQL. ( I do the filtering the same way, using the "RowFilter" property).

You can see/feel it working in a demo of my app, BugTracker.NET, at http://ifdefined.com/btnet/bugs.aspx

---

You should avoid the SQL Server sorting, unless if necessary. Why not sort on app server or client side? Also .NET Generics does exceptional sortin

참고URL : https://stackoverflow.com/questions/149380/dynamic-sorting-within-sql-stored-procedures