Пару слов о coalesce

Пару лет назад я уже писал о том как можно передавать массив элементов в хранимую процедуру и уже там поочередно обращаться к каждому элементу по отдельности. Из возможных вариантов были CSV-строки и XML.

И вот совсем недавно у меня появилась необходимость сделать как бы обратную операцию – скомпоновать результат выполнения SELECT в одну CSV-строку  для возврата в вызываемый код. Другими словами дано – произвольный SELECT по одной колонке, который возвращает скажем  10 элементов. Необходимо эти элементы объеденить в одну строку, разделяя элмененты запятой, и вернуть эту строку, как результат вызовы хранимой процедуры.

Одним из решений данной задачи я и хочу поделится в публикации. Решение это использует достаточно экзотическую (по крайней мере для меня) функцию T-SQL – COALESCE .

В документации написано, что данная функция вычисляет аргументы в указанном порядке и возвращает текущее значение первого выражения, которое изначально не дает значение NULL.

Отдаленно функция чем то напоминает ISNULL, и возможно даже CASE в определенных условиях. Но сама по себе это функция намного более эффективная и гибкая, ввиду того что в отличие от того же ISNULL, входные значения для выражения COALESCE могут вычисляться несколько раз, то есть если использоваться ее вместе с SELECT – она будет вызвана поочередно для каждой возвращаемой строки.

Итак, давайте вернемся к изначальной задаче.

Предположим у нас есть вот такой запросб который возвращает список имен заказчиков:

SELECT ContactName FROM Customers WHERE Country = 'France'

ContactName

------------------------------

Frédérique Citeaux

Laurence Lebihan

Janine Labrune

Martine Rancé

Carine Schmitt

Daniel Tonini

Annette Roulet

Marie Bertrand

Dominique Perrier

Mary Saveley

Paul Henriot

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

DECLARE @AllContacts nvarchar(max)

SET @AllContacts = NULL

select @AllContacts = coalesce(@AllContacts + ', ', '') + ContactName FROM Customers WHERE Country = 'France'

SELECT @AllContacts AS CSV

Результатом его выполнения будет как вы уже догадались – то что нам нужно.

CSV

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Frédérique Citeaux, Laurence Lebihan, Janine Labrune, Martine Rancé, Carine Schmitt, Daniel Tonini, Annette Roulet, Marie Bertrand, Dominique Perrier, Mary Saveley, Paul Henriot

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

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