Динамическая сортировка в рамках хранимых процедур SQL

Да, это - боль и способ, которым Вы делаете, это выглядит подобным тому, что я делаю:

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 из кода, который превращается в масштабируемость и кошмар обслуживания для DBAs.

то, Что я делаю из кода, осуществляют рефакторинг подкачку страниц и сортирующий, таким образом, у меня, по крайней мере, нет большого повторения там с заполнением значений для @SortExpr и @SortDir.

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

Необходимо избежать сортировки SQL Server, если при необходимости. Почему не вид на сервере приложений или стороне клиента? Также Дженерики.NET делают исключительную сортировку

Как насчет того, чтобы обработать сортировку на материале, отображающем результаты - сетки, отчеты, и т.д. а не на SQL?

РЕДАКТИРОВАНИЕ:

Для разъяснения вещей, так как этот ответ спустился - проголосовавший ранее, я уточню немного...

Вы заявили, что знали о клиентской сортировке, но хотели избегать ее. Это - Ваш вызов, конечно.

то, На что я хочу указать, тем не менее, - то, что путем выполнения его на клиентском, Вы можете вытянуть данные ОДНАЖДЫ, и затем работать с ним однако Вы хотите - по сравнению с выполнением нескольких прохождений назад и вперед в сервер каждый раз, когда вид изменяется.

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

при использовании какого-либо более нового материала ASP.NET для отображения в сети много того материала уже испеклось прямо в.

это стоящий добавления так много кода к каждой хранимой процедуре только для обработки сортировки? Снова, Ваш вызов.

я не тот, который в конечном счете ответит за поддержку этого. Но уделите некоторое внимание тому, что будет включено, поскольку столбцы добавляются/удаляются в различных наборах данных, используемых хранимыми процедурами (требующий модификаций к Операторам выбора) или когда внезапно вместо того, чтобы сортировать по двум столбцам, пользователь решает, что им нужно три - требование, чтобы Вы теперь обновили каждые из Ваших хранимых процедур, которые используют этот метод.

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

Это решение могло бы только работать в.NET, я не знаю.

я выбираю данные в C# с начальным порядком сортировки в порядке SQL пунктом, помещаю те данные в DataView, кэширую его в Переменной сеанса и использую его для создания страницы.

, Когда пользователь нажимает на заголовок столбца к виду (или страница или фильтр), я не возвращаюсь к базе данных. Вместо этого я возвращаюсь в свой кэшируемый DataView и устанавливаю его свойство "Sort" на выражение, которое я создаю динамично, точно так же, как я был бы динамический SQL. (Я делаю фильтрацию того же пути, с помощью свойства "RowFilter").

можно видеть/чувствовать, что он работает в демонстрации моего приложения, BugTracker.NET, в http://ifdefined.com/btnet/bugs.aspx

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

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

я не потел бы он.

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

1. Делают хранимую процедуру функцией, которая возвращает таблицу Ваших значений - никакой вид.
2. Затем в Вашем коде приложения делают select * from dbo.fn_myData() where ... order by ..., таким образом, можно динамично указать порядок сортировки там.

Затем, по крайней мере, динамическая часть находится в Вашем приложении, но база данных все еще делает тяжелый подъем.

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

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

Протест: Я не протестировал это, но это "должно" работать в SQL Server 2005 (который составит временную таблицу от набора результатов, не указывая столбцы заранее.)

Существует несколько различных способов, в которых можно взломать это.

Предпосылки:

1. Только один оператор SELECT в sp
2. Не учитывает любую сортировку (или имейте значение по умолчанию)

Затем вставляют во временную таблицу:

create table #temp ( your columns )

insert #temp
exec foobar

select * from #temp order by whatever

Метод № 2: настройте связанный сервер назад к себе, затем выберите из этого использования openquery: http://www.sommarskog.se/share_data.html#OPENQUERY

Динамический SQL является все еще опцией. Просто необходимо решить, более ли та опция приемлема, чем, что Вы в настоящее время имеете.

Вот статья, которая показывает что: http://www.4guysfromrolla.com/webtech/010704-1.shtml .

Этот подход мешает поддающимся сортировке столбцам дублироваться дважды в порядке и является немного большим количеством читаемого 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

В какой-то момент разве это не становится стоящим того, чтобы переехать от хранимых процедур и просто использовать параметризированные запросы для предотвращения этого вида хакерства?

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

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

Тогда вам просто нужно убедиться, что число находится в пределах от 1 до # столбцов. Вы могли бы даже расширить это до списка номеров столбцов и разобрать его в таблицу целых чисел, используя функцию, подобную this . Затем вы должны построить порядок по пунктам, например, так ...

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

Один недостаток состоит в том, что вы должны помнить порядок каждого столбца на стороне клиента. Особенно, когда вы не отображаете все столбцы или отображаете их в другом порядке. Когда клиент хочет отсортировать, вы сопоставляете имена столбцов с порядком столбцов и генерируете список целых.

Техника хранимой процедуры (хак?), которую я использовал, чтобы избежать динамического SQL для некоторых заданий, заключается в том, чтобы иметь уникальный столбец сортировки. Т.е.,

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

Этот столбец легко подчинить - вы можете объединить поля в столбце mySort, изменить порядок с помощью математических функций или функций даты и т.д.

Предпочтительно, однако, я использую свои asp.net gridviews или другие объекты со встроенной сортировкой, чтобы сделать сортировку за меня ПОСЛЕ получения данных из Sql-сервера. Или даже если это не встроено - например, таблицы данных и т.д. в asp.net.