Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Подскажите как лучше реализовать сортировку чтобы на входе ХП подавалолсь поле?  [new]
stinggga
Member

Откуда:
Сообщений: 416
Подскажите как лучше реализовать сортировку чтобы на входе ХП подавалолсь поле?

Нужно нормальное решение с asc/desc
31 авг 15, 13:14    [18090645]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите как лучше реализовать сортировку чтобы на входе ХП подавалолсь поле?  [new]
invm
Member

Откуда: Москва
Сообщений: 9827
На вход ХП не может "подаваться" поле. Какой-либо признак в виде переменной - может.
А реализовать можно через множество case в order by.
31 авг 15, 13:22    [18090678]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите как лучше реализовать сортировку чтобы на входе ХП подавалолсь поле?  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
динамический sql либо извращение с CASE WHEN в ORDER BY. можно пример Вашего запрос увидеть?
31 авг 15, 13:22    [18090681]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите как лучше реализовать сортировку чтобы на входе ХП подавалолсь поле?  [new]
stinggga
Member

Откуда:
Сообщений: 416
invm,

Я имел ввиду название поля. А есть какой-то примерчик чтобы с типом сортировке
31 авг 15, 13:23    [18090683]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите как лучше реализовать сортировку чтобы на входе ХП подавалолсь поле?  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
CREATE PROCEDURE dbo.test1
(
	@column SYSNAME, @is_desc BIT
)
AS BEGIN
	
	DECLARE @SQL NVARCHAR(MAX)
    SET @SQL = 'SELECT * FROM sys.objects ORDER BY ' + @column + CASE WHEN @is_desc = 1 THEN ' DESC' ELSE '' END
    
    --PRINT @SQL
    EXEC sys.sp_executesql @SQL

END
GO

EXEC dbo.test1 @column = 'name'
31 авг 15, 13:25    [18090690]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите как лучше реализовать сортировку чтобы на входе ХП подавалолсь поле?  [new]
sphinx_mv
Member [заблокирован]

Откуда:
Сообщений: 1672
AlanDenton
CREATE PROCEDURE dbo.test1
(
	@column SYSNAME, @is_desc BIT
)
AS BEGIN
	
	DECLARE @SQL NVARCHAR(MAX)
    SET @SQL = 'SELECT * FROM sys.objects ORDER BY ' + @column + CASE WHEN @is_desc = 1 THEN ' DESC' ELSE '' END
    
    --PRINT @SQL
    EXEC sys.sp_executesql @SQL

END
GO

EXEC dbo.test1 @column = 'name'
Настоятельная рекомендация помедитировать на тему "SQL injection"
31 авг 15, 13:29    [18090704]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите как лучше реализовать сортировку чтобы на входе ХП подавалолсь поле?  [new]
stinggga
Member

Откуда:
Сообщений: 416
AlanDenton,

А если без сборки строки sql а просто живим sql кодом
31 авг 15, 13:30    [18090719]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите как лучше реализовать сортировку чтобы на входе ХП подавалолсь поле?  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
DECLARE
	  @column SYSNAME = 'name'
	, @order TINYINT = -1 -- 1 - asc; -1 - desc

;WITH cte AS 
(
    SELECT *
		, s_name = ROW_NUMBER() OVER (ORDER BY name)
		, s_object_id = ROW_NUMBER() OVER (ORDER BY [object_id])
	FROM sys.objects  
)
SELECT * 
FROM cte
ORDER BY @order *
	CASE @column
		WHEN 'name' THEN s_name
		WHEN 'object_id' THEN s_object_id
	END
31 авг 15, 13:41    [18090778]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите как лучше реализовать сортировку чтобы на входе ХП подавалолсь поле?  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
sphinx_mv
Настоятельная рекомендация помедитировать на тему "SQL injection"


это ко мне вопрос? или вообще... :)

и забыл в примере выше @order TINYINT => INT иначе получим "Arithmetic overflow error for data type ..."
31 авг 15, 13:44    [18090796]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите как лучше реализовать сортировку чтобы на входе ХП подавалолсь поле?  [new]
stinggga
Member

Откуда:
Сообщений: 416
AlanDenton
DECLARE
	  @column SYSNAME = 'name'
	, @order TINYINT = -1 -- 1 - asc; -1 - desc

;WITH cte AS 
(
    SELECT *
		, s_name = ROW_NUMBER() OVER (ORDER BY name)
		, s_object_id = ROW_NUMBER() OVER (ORDER BY [object_id])
	FROM sys.objects  
)
SELECT * 
FROM cte
ORDER BY @order *
	CASE @column
		WHEN 'name' THEN s_name
		WHEN 'object_id' THEN s_object_id
	END


Тут нет типа сортивроки asc desc? Я собственно поэтому и открыл тему
31 авг 15, 14:03    [18090906]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите как лучше реализовать сортировку чтобы на входе ХП подавалолсь поле?  [new]
invm
Member

Откуда: Москва
Сообщений: 9827
AlanDenton
это ко мне вопрос?
К вам. Ваш вариант с DSQL подвержен SQL-injection.
AlanDenton
DECLARE
	  @column SYSNAME = 'name'
	, @order TINYINT = -1 -- 1 - asc; -1 - desc

;WITH cte AS 
(
    SELECT *
		, s_name = ROW_NUMBER() OVER (ORDER BY name)
		, s_object_id = ROW_NUMBER() OVER (ORDER BY [object_id])
	FROM sys.objects  
)
SELECT * 
FROM cte
ORDER BY @order *
	CASE @column
		WHEN 'name' THEN s_name
		WHEN 'object_id' THEN s_object_id
	END
Зачем столько ненужных сортировок?
DECLARE
	  @column SYSNAME = 'object_id'
	, @order SMALLINT = 1 -- 1 - asc; -1 - desc

SELECT * 
FROM sys.objects
ORDER BY
	CASE WHEN @column = 'name' and @order = 1 then name end,
	CASE WHEN @column = 'name' and @order = -1 then name end desc,
	CASE WHEN @column = 'object_id' and @order = 1 then object_id end,
	CASE WHEN @column = 'object_id' and @order = -1 then object_id end desc;
31 авг 15, 14:06    [18090915]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите как лучше реализовать сортировку чтобы на входе ХП подавалолсь поле?  [new]
invm
Member

Откуда: Москва
Сообщений: 9827
stinggga
Тут нет типа сортивроки asc desc?
Есть. Только сделано неявно и без учета значения null.
31 авг 15, 14:07    [18090922]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите как лучше реализовать сортировку чтобы на входе ХП подавалолсь поле?  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
invm
Зачем столько ненужных сортировок?


DECLARE
	  @column SYSNAME = 'object_id'
	, @order INT = 1 -- 1 - asc; -1 - desc

SELECT * 
FROM sys.objects
ORDER BY
	CASE WHEN @column = 'name' AND @order = 1 THEN name END,
	CASE WHEN @column = 'name' AND @order = -1 THEN name END DESC,
	CASE WHEN @column = 'object_id' AND @order = 1 THEN [object_id] END,
	CASE WHEN @column = 'object_id' AND @order = -1 THEN [object_id] END DESC
OPTION(RECOMPILE)

SELECT *
FROM sys.objects
ORDER BY @order *
	CASE @column
		WHEN 'name' THEN ROW_NUMBER() OVER (ORDER BY name)
		WHEN 'object_id' THEN ROW_NUMBER() OVER (ORDER BY [object_id])
	END
OPTION(RECOMPILE)


не думаю что наши решения кардинально отличаются. Мы даже не знаем, что за запрос у @stinggga. Мне мой вариант нравится за минимум кода. Про эффективность никто не говорил.

invm
Ваш вариант с DSQL подвержен SQL-injection.


Этого я не отрицал. Просто привел как самый простой и очевидный вариант.

Кстати, мне интересны другие варианты. Раз уж зашла тема об эффективности :)
31 авг 15, 14:21    [18090995]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите как лучше реализовать сортировку чтобы на входе ХП подавалолсь поле?  [new]
Ivan Durak
Member

Откуда: Minsk!!!
Сообщений: 3752
AlanDenton

Кстати, мне интересны другие варианты. Раз уж зашла тема об эффективности :)

К примеру не сортировать в процедуре.
31 авг 15, 14:43    [18091152]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите как лучше реализовать сортировку чтобы на входе ХП подавалолсь поле?  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
Я придерживаюсь такого же мнения. Но все же :)
31 авг 15, 14:47    [18091177]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите как лучше реализовать сортировку чтобы на входе ХП подавалолсь поле?  [new]
invm
Member

Откуда: Москва
Сообщений: 9827
AlanDenton
Мне мой вариант нравится за минимум кода.
Странный критерий оптимизации.
AlanDenton
Просто привел как самый простой и очевидный вариант.
Простой и очевидный вариант, приводящий к проблемам - простой, очевидный и хреновый вариант.
AlanDenton
не думаю что наши решения кардинально отличаются
AlanDenton
Кстати, мне интересны другие варианты. Раз уж зашла тема об эффективности :)
+
use tempdb;
go

if object_id('tempdb..#t', 'U') is not null
 drop table #t;
go

select top (1000000)
 a.*
into
 #t
from
 sys.objects a cross join
 sys.objects b cross join
 sys.objects c;

DECLARE
	  @column SYSNAME = 'name'
	, @order INT = 1 -- 1 - asc; -1 - desc
, @x int;

declare @s nvarchar(max) = N'declare @x int; select @x = schema_id from #t order by ' + quotename(@column) + N' ' + case when @order = 1 then N' asc' else N' desc' end + N' option (maxdop 1);';

set statistics time on;

SELECT @x = schema_id 
FROM #t
ORDER BY
	CASE WHEN @column = 'name' AND @order = 1 THEN name END,
	CASE WHEN @column = 'name' AND @order = -1 THEN name END DESC,
	CASE WHEN @column = 'object_id' AND @order = 1 THEN [object_id] END,
	CASE WHEN @column = 'object_id' AND @order = -1 THEN [object_id] END DESC
OPTION(maxdop 1)

SELECT @x = t.schema_id 
FROM #t t cross apply
(select case when @column = N'name' then cast(t.name as sql_variant) when @column = N'object_id' then cast(str(object_id) as sql_variant) end) a(v)
ORDER BY
	CASE WHEN @order = 1 THEN a.v END,
	CASE WHEN @order = -1 THEN a.v END DESC
OPTION(maxdop 1);

SELECT @x = schema_id 
FROM #t
ORDER BY @order *
	CASE @column
		WHEN 'name' THEN ROW_NUMBER() OVER (ORDER BY name)
		WHEN 'object_id' THEN ROW_NUMBER() OVER (ORDER BY [object_id])
	END
OPTION(maxdop 1)

exec(@s);

set statistics time off;
go

if object_id('tempdb..#t', 'U') is not null
 drop table #t;
go
31 авг 15, 15:10    [18091319]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите как лучше реализовать сортировку чтобы на входе ХП подавалолсь поле?  [new]
stinggga
Member

Откуда:
Сообщений: 416
Воспользовался примером

,@SortField varchar(150) = ''



ORDER BY @SortField *
CASE @SortField
WHEN null THEN PointName
WHEN 'PointID' THEN p.PointID
when 'PointName' then PointName
WHEN 'city' THEN Location.FullAddress
END

Пишет ошибку:
Conversion failed when converting the varchar value 'PointID' to data type int.

Подскажите в чём дело, пожалуйста.
31 авг 15, 15:20    [18091387]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите как лучше реализовать сортировку чтобы на входе ХП подавалолсь поле?  [new]
Glory
Member

Откуда:
Сообщений: 104751
stinggga
Подскажите в чём дело, пожалуйста.

В том, что все значения, которые возвращает CASE, должны быть одного типа
31 авг 15, 15:55    [18091628]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите как лучше реализовать сортировку чтобы на входе ХП подавалолсь поле?  [new]
stinggga
Member

Откуда:
Сообщений: 416
Glory,

А если надо сортировать по какому-то полю, которое получаем как входной параметр, и это надо сделать не сбором в переменную @SQL и вызовом строки EXEC sys.sp_executesql @SQL, как это можно сделать?
31 авг 15, 16:24    [18091881]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите как лучше реализовать сортировку чтобы на входе ХП подавалолсь поле?  [new]
Glory
Member

Откуда:
Сообщений: 104751
stinggga
А если надо сортировать по какому-то полю, которое получаем как входной параметр, и это надо сделать не сбором в переменную @SQL и вызовом строки EXEC sys.sp_executesql @SQL, как это можно сделать?

Вы что не читали ответы в данной теме ?
31 авг 15, 16:26    [18091891]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите как лучше реализовать сортировку чтобы на входе ХП подавалолсь поле?  [new]
stinggga
Member

Откуда:
Сообщений: 416
Glory,
Читал
31 авг 15, 16:37    [18091954]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите как лучше реализовать сортировку чтобы на входе ХП подавалолсь поле?  [new]
stinggga
Member

Откуда:
Сообщений: 416
stinggga,

Где вы увидели рабочий пример ч учётом пожеланий которые я просил?
31 авг 15, 16:42    [18091986]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите как лучше реализовать сортировку чтобы на входе ХП подавалолсь поле?  [new]
Glory
Member

Откуда:
Сообщений: 104751
stinggga
Где вы увидели рабочий пример ч учётом пожеланий которые я просил?

Вот здесь, например, 18090915
31 авг 15, 16:43    [18091993]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите как лучше реализовать сортировку чтобы на входе ХП подавалолсь поле?  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
Вам их привели минимум 4. Два моих и два от @invm. Понятие "нормальное" решение в Вашей задаче отсутствует. Почему.... Потому что, насколько мне известно, путей только два - динамический SQL либо CASE WHEN/IIF.
31 авг 15, 16:45    [18091997]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите как лучше реализовать сортировку чтобы на входе ХП подавалолсь поле?  [new]
stinggga
Member

Откуда:
Сообщений: 416
Glory,
Мне он не подходит так как поля должны быть только одного типа
31 авг 15, 16:49    [18092016]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить