MS SQL 2011 (Denali) – With Result Set

Модификация возвращаемого набора данных (NEW)

В оригинальном звучании и в жизни эта возможность звучит как With Result Set. Эта штука позволяет менять имена и типы данных в возвращаемом хранимой процедурой наборе данных.

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

Для демонстрации работы будем использовать в качестве примера таблицу tbl_Test состоящую из 3 колонок.

Теперь запишем туда немного информации. Пусть это будет 1000 записей:

! Рекомендую взять скрипт на заметку!

Выполнение скрипта выше выведет примерно следующий набор данных (часть)

 

Теперь напишем процедуру, которая будет выводить данные запроса к таблице tbl_Test

 

Для того, чтобы получить итоговый набор данных по результатам выполнения хранимой процедуры существует несколько подходов. Некоторые из них описываются и обсуждаются в статье Эрланда Зомарскога (Erland Sommarskog) в его статье. Мы воспользуемся одним из подходов основанном на временных таблицах.

Использование временных таблиц

Подход описанный выше работает на отлично, если мы заранее знаем какие колонки и какого типа требуются на выходе.

Недостатки этого и похожих подходов:

  • Ни один из подходов не предлагает прямого решения проблемы. В любом случае требуется поддержка со стороны временных таблиц или же переменных. Потребляет место в базе данных, под временную таблицу.
  • Время выполнение запроса увеличивается
  • В случае, когда требуется Open Raw Set или Open query запросы, необходимо явным образом включить функцию Ad Hoc Distributed Queries и только потом начинать работу.
  • В случае задействования временных таблиц или переменных табличного типа, нужно заранее знать структуру ответа процедуры.

Новый подход MS SQL 2011

Новая версия позволяет избавится от упомянутых недостатков и сейчас мы увидим как именно.

Вывод данных будет такой:

Общий синтаксис использования With Result Set

 

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

Т.е. в примере ниже база выдаст ошибку о неправомерном приведении типов. Мы пытаемся вернуть тип int в то время как поле объявлено как varchar(50).

Во время выполнения скрипта получим следующую ошибку:

Msg 8114, Level 16, State 2, Procedure Usp_FetchRecords, Line 5 Error converting data type varchar to int.

Запрос, который был продемонстрирован выше предназначен для преобразования одиночного результирующего набора данных с применением With Result Set. Однако эта техника, как можно видеть из общего синтаксиса может быть распространена на несколько итоговых наборов. Сейчас будет пример как это сделать.

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

 

Второй select возвращает абонентов с четными телефонными номерами. Пример выполнения может быть такой (часть результата)

Теперь попробуем применить With Result Set, чтобы получить более удобоваримый результат без изменения самой хранимой процедуры.

Результат выполнения теперь будет в духе:

В данном случае хранимая процедура возвращает два результирующих набора данных, но если мы попробуем обработать в With Result Set только один из них, то получим ошибку от SQL движка.

Msg 11535, Level 16, State 1, Procedure Usp_ModifiedFetchRecords, Line 11 EXECUTE statement failed because its WITH RESULT SETS clause specified 1 result set(s), and the statement tried to send more result sets than this.

Способ получения данных из With Result Set

Порой может потребоваться дополнительно обработать значение, полученное с помощью With Result Set. В таком случае можно использовать временные таблицы или переменный табличного типа.

Рассмотрим подход  с использованием переменных табличного типа.

Результат будет ожидаемым, имя работника сократиться до 6 символов. Это можно увидеть на следующем скриншоте (последние 10 записей)

Возможное применение:

  1. Преобразование данных в SSIS пакетах будет проще, дополнительное описание смотрите в статье здесь.
  2. Изменение типов данных без изменения схемы. Представьте что .Net приложение ожидает значение булевого типа, а значение в таблице закодировано типом int или char(1). В общем можно применить конвертацию значений с помощью конструкции Case When Then Else. Но ведь проще и приятнее сразу изменить тип данных на bit (в случае с int).
  3. Еще один пример приложения With Result Set, когда .Net программа ждет  int, а в базе данных колонка имеет тип float.
  4. Возможная невосприимчивость DAL к изменениям схемы. Имеется в виду положительная невосприимчивость, когда с помощью With Result Set задаем имена колонок для результирующего набора данных. Тогда будет неважно как имена меняются в самой базе. Эдакий аналог VIEW для хранимых процедур.

Ограничения:

Нельзя делать выборочное изменение колонок в итоговом наборе данных. Например следующий скрипт вызовет ошибку при выполнении:

Так как процедура возвращает набор из трех колонок. Ошибка будет такая:

Msg 11537, Level 16, State 1, Procedure Usp_FetchRecords, Line 5 EXECUTE statement failed because its WITH RESULT SETS clause specified 2 column(s) for result set number 1, but the statement sent 3 column(s) at run time.

Дальше будет так же интересно, так что оставайтесь на связи. Перевод.

Hard’n’heavy!

 

 

Оставить комментарий