Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 SELECT странно присваивает значение переменной, если ORDER BY вместе с CASE  [new]
topic starter
Member

Откуда:
Сообщений: 245
Всем привет.

Ну вот хотим мы посмотреть список полей в таблицах, сортируя (по желанию) или по названию таблицы, или по названию поля. Тут все окей, видим длинный список:
declare @sort_order bit = 0;
select o.name + '.' + c.name
	from sys.columns as c
	join sys.objects as o on o.object_id = c.object_id
	order by
		case @sort_order
			when 0 then o.name
			when 1 then c.name
		end;


Теперь хотим этот список записать в символьную переменную. Сортировку пока зададим жестко. Тут тоже все окей, видим в переменной длинный список полей через запятую:
declare @col_list varchar(max) = '';
select
	@col_list += 
		CASE @col_list WHEN '' THEN '' ELSE ',' END 
		+ o.name + '.' + c.name
	from sys.columns as c
	join sys.objects as o on o.object_id = c.object_id
	order by o.name;
select @col_list;


А теперича хотим этот список не только в переменную загнать, но и увидеть отсортированным по нашему желанию:
declare @sort_order bit = 0;
declare @col_list varchar(max) = '';
select
	@col_list += 
		CASE @col_list WHEN '' THEN '' ELSE ',' END 
		+ o.name + '.' + c.name
	from sys.columns as c
	join sys.objects as o on o.object_id = c.object_id
	order by
		case @sort_order
			when 0 then o.name
			when 1 then c.name
		end;
select @col_list;

И опа! видим, что в переменную записалось только ОДНА (!) строка выборки.

Вопрос: как сделать, чтобы в переменную записалась вся выборка, а не одна-едиенственная строка, когда в ORDER BY стоит CASE? Наверняка кто-то сталкивался с этим.

Заранее большое спасибо.
SELECT @@VERSION
Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64)
Jun 17 2011 00:54:03
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit)
30 сен 13, 10:32    [14899964]     Ответить | Цитировать Сообщить модератору
 Re: SELECT странно присваивает значение переменной, если ORDER BY вместе с CASE  [new]
invm
Member

Откуда: Москва
Сообщений: 9845
http://support.microsoft.com/kb/287515/en-us
30 сен 13, 10:42    [14900004]     Ответить | Цитировать Сообщить модератору
 Re: SELECT странно присваивает значение переменной, если ORDER BY вместе с CASE  [new]
MasterZiv
Member

Откуда: Питер
Сообщений: 34705
topic starter,

Вопрос: как сделать, чтобы в переменную записалась вся выборка, а не одна-едиенственная строка, когда в ORDER BY стоит CASE? Наверняка кто-то сталкивался с этим.

SQL это делать не умеет.
Это надо делать курсором на данный SELECT, в цикле накапливать переменную.
Либо можно ещё CONCAT AGREGATE , но не знаю, есть ли он в MSSQL сейчас.
30 сен 13, 10:43    [14900008]     Ответить | Цитировать Сообщить модератору
 Re: SELECT странно присваивает значение переменной, если ORDER BY вместе с CASE  [new]
topic starter
Member

Откуда:
Сообщений: 245
invm, спасибо за ссылку. Теперь стало понятно, что если разные планы генерируются, то предсказуемого результата ожидать нечего.

MasterZiv, про CONCAT AGGREGATE, к сожалению, не удалось ничего найти в BOL. А с курсором, конечно, неохота связываться, в производственной БД курсор станет тормозить на больших выборках.

Ладно, попробую обходные пути, подумаю еще.
30 сен 13, 11:04    [14900110]     Ответить | Цитировать Сообщить модератору
 Re: SELECT странно присваивает значение переменной, если ORDER BY вместе с CASE  [new]
Сон Веры Павловны
Member

Откуда:
Сообщений: 6203
MasterZiv
Это надо делать курсором на данный SELECT, в цикле накапливать переменную

https://www.sql.ru/faq/faq_topic.aspx?fid=130
последние 2 варианта. Вариант с рекурсивным CTE не советую - очень медленный и прожорливый.
30 сен 13, 11:11    [14900155]     Ответить | Цитировать Сообщить модератору
 Re: SELECT странно присваивает значение переменной, если ORDER BY вместе с CASE  [new]
aleks2
Guest
topic starter
invm, спасибо за ссылку. Теперь стало понятно, что если разные планы генерируются, то предсказуемого результата ожидать нечего.

MasterZiv, про CONCAT AGGREGATE, к сожалению, не удалось ничего найти в BOL. А с курсором, конечно, неохота связываться, в производственной БД курсор станет тормозить на больших выборках.

Ладно, попробую обходные пути, подумаю еще.


Выборка в промежуточную временную таблицу с правильным кластерным индексом - решает вашу проблему.
30 сен 13, 11:22    [14900247]     Ответить | Цитировать Сообщить модератору
 Re: SELECT странно присваивает значение переменной, если ORDER BY вместе с CASE  [new]
topic starter
Member

Откуда:
Сообщений: 245
aleks2, именно так и планировал сделать.
30 сен 13, 12:10    [14900567]     Ответить | Цитировать Сообщить модератору
 Re: SELECT странно присваивает значение переменной, если ORDER BY вместе с CASE  [new]
topic starter
Member

Откуда:
Сообщений: 245
Сон Веры Павловны,

этот вариант, который вы предложили, я знаю. Я им постоянно пользуюсь для конкатенации строк. В этот раз просто захотелось обойтись без FOR XML PATH, попробовать другой путь - похоже, зря захотелось :-)
30 сен 13, 12:24    [14900677]     Ответить | Цитировать Сообщить модератору
 Re: SELECT странно присваивает значение переменной, если ORDER BY вместе с CASE  [new]
Shakill
Member

Откуда: мск
Сообщений: 1887
aleks2
Выборка в промежуточную временную таблицу с правильным кластерным индексом - решает вашу проблему.

обеспечивать корректность выборки за счёт структуры - несколько кривоватое решение. imho, решение с тем же for xml красивее
30 сен 13, 12:30    [14900708]     Ответить | Цитировать Сообщить модератору
 Re: SELECT странно присваивает значение переменной, если ORDER BY вместе с CASE  [new]
Сон Веры Павловны
Member

Откуда:
Сообщений: 6203
topic starter
захотелось обойтись без FOR XML PATH

Почему? Есть какие-то нарекания к запросам с этой инструкцией? Или просто захотелось, и всё?
Вот здесь: 14761519 дело дошло до сравнения производительности. Вариант с FOR XML оказался самым быстрым.
30 сен 13, 13:41    [14901272]     Ответить | Цитировать Сообщить модератору
 Re: SELECT странно присваивает значение переменной, если ORDER BY вместе с CASE  [new]
topic starter
Member

Откуда:
Сообщений: 245
Сон Веры Павловны,

нет, нареканий никаких нет, нормально работает for xml во всех запросах в базе. Просто захотелось как-то по-другому попробовать. Инициатива, как известно, наказуема :-) Что вариант for xml оказался самым производительным - это новость, интересно было узнать, спасибо.
30 сен 13, 14:11    [14901621]     Ответить | Цитировать Сообщить модератору
 Re: SELECT странно присваивает значение переменной, если ORDER BY вместе с CASE  [new]
мимо
Guest
Shakill
aleks2
Выборка в промежуточную временную таблицу с правильным кластерным индексом - решает вашу проблему.

обеспечивать корректность выборки за счёт структуры - несколько кривоватое решение.

Это не кривое решение, это понимание T-Sql.
30 сен 13, 14:24    [14901724]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить