Пару лет назад я уже писал о том
как можно передавать массив элементов в хранимую процедуру и уже там поочередно
обращаться к каждому элементу по отдельности. Из возможных вариантов были 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 в умелых руках является чрезвычайно гибким и
эффективным инструментом, которая в определенных случаях позволит вам
эффективно и лакнично решить ваши задачи.
