SQL хранимая процедура для трансформации таблицы. Динамический запрос.

В данном посте я немного затрону вопросы создания T-SQL хранимых процедур и динамических запросов.

В одном из моих предыдущих постов - http://vasyniuk.blogspot.com/2012/03/pivot-unpivot.html я рассказывал об обработке результирующих наборов данных с использованием оператора T-SQL PIVOT. В данном посте я хочу развить эту тему и перевести ее на более высокий уровень – на уровень использования хранимых процедур SQL.

В вышеупомянутом посте шел разговор о преобразовании небольшой таблицы, которая являлась результатом выполнения UDF. Как быть если необходимо обработать результат выполнения UDF с большим множеством полей, который после преобразования должен превратиться в таблицу большого размера? Если случай не тривиальный и результирующая таблица большого размера должна содержать, допустим, 20 столбцов, то придется вручную прописывать эти 20 имен столбцов в операторе выборки SELECT, что не есть хорошо.

Очевидно, что в таком случае необходима автоматизация процесса преобразования наборов данных.  Единственный разумный вариант работы с такой таблицей – динамическая обработка ее столбцов в запросе с использованием оператора PIVOT.

Чтобы извлечь максимальную выгоду из такой ситуации, я буду использовать не прямой запроc, а хранимую процедуру Transact-SQL. Сразу скажу, что разработка такой процедуры с точки зрения программирования под MS SQL Server – несет в себе много скрытых препятствий для неопытных разработчиков. Создается впечатление, что в Microsoft попытались максимально усложнить жизнь разработчикам, наполнив T-SQL большим количеством всевозможных ограничений.

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

Ограничение: для выполнения динамического запроса необходимо использовать инструкцию EXECUTE, использование которой не допускается в UDF.

Сценарий разработки

Как вариант, можно спроектировать хранимую процедуру, в которую будет передаваться таблица-переменная. Внутри пакета хранимой процедуры будет происходить обработка этой таблицы с последующим формированием результирующего набора данных – преобразованной таблицы.

CREATE PROC [dbo].[piv_StatTable] @temp_table dbo.EStatTT READONLY

AS

     SET NOCOUNT ON

     DECLARE @rows varchar(8000),@querry varchar(8000)

     CREATE TABLE #df(element_name nvarchar(4),period_id tinyint, value smallint)

     INSERT #df SELECT * FROM @temp_table

     SELECT @rows=COALESCE(@rows+',['+CAST(period_id AS varchar)+']',

                                        '['+CAST(period_id AS varchar)+']')

     FROM @temp_table

     GROUP BY period_id

    

     SET @querry='SELECT * FROM #df PIVOT (MAX(value) FOR period_id IN('+@rows+')) AS tbl_pivot'

     EXEC(@querry)

    

     DROP TABLE #df

GO

Разбор полетов

На входе в процедуру передается переменная @temp_table типа dbo.EStatTT. Тип данных EStatTT – это определяемый пользователем табличный тип. Табличный тип данных TABLE – относительно молодой поддерживаемый тип в MS SQL Server по сравнению с такими привычными многим типами как int или varchar. Оперировать этим типом данных можно, допустим, в UDF - возвращая таблицу данных, как результат выполнения функции. В контексте передачи переменных хранимым процедурам тип TABLE использовать не получится, хотя хотелось бы.

Ограничение: для этого необходимо сначала создать собственный тип данных - определяемый пользователем табличный тип (по аналогии с определением класса на C#).

После объявления такого типа данных его можно использовать для передачи переменных в качестве параметров хранимым процедурам (ссылка на MSDN в конце поста).

CREATE TYPE [dbo].[EStatTT] AS TABLE(

     [element_name] [nvarchar](4) NULL,

     [period_id] [tinyint] NULL,

     [value] [smallint] NULL

)

GO

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

Еще одним нюансом использования переменных табличного типа в качестве параметров, передаваемых хранимым процедурам, является ограничение на выполнение над ними DML операций в теле хранимых процедур. Как видно, после декларации параметра в объявлении хранимой процедуры следует инструкция READONLY, которая и определяет поведение объекта-таблицы внутри процедуры.

Зачем необходима временная таблица #df?

Как видно из листинга, #df используется в строковой переменной @querry при составлении тела динамического запроса.

Ограничение: мы не можем использовать переменную @temp_table подобным образом – это своего рода ограничение видимости локальных переменных.

@temp_table – является локальной переменной, объявление и инициализация которой будут происходить вне пакета разрабатываемой хранимой процедуры. В динамических запросах нельзя использовать локальные переменные, которые были объявлены до вызова самого запроса. Собственно, именно по этой причине мы вынуждены использовать временную таблицу, которая заполняется данными из @temp_table путем выполнения простого запроса INSERT…SELECT.

Переменная строкового типа @rows содержит строку сформированную с помощью оператора COALESCE из значений столбца “period_id” таблицы, которую мы передаем процедуре. С первого взгляда оператор инициализации переменной @rows может показаться немного запутанным, но в нем нет ничего экстраординарного.

Оператор COALESCE – это один из CASE-производных операторов, который упрощает синтаксис Transact-SQL. Подробно о нем можно прочитать в MSDN - http://msdn.microsoft.com/ru-ru/library/ms190349.aspx. Чтобы было понятнее для чего он служит в данном случае, можно провести аналогию с оператором C# “??”:

string rows = (rows + ",[" + period_id.ToString() + "]") ?? ("[" + period_id.ToString() + "]");

Уникальность значений обеспечивается путем использования инструкции GROUP BY в конце запроса выборки.

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

В переменной @querry формируется тело запроса с использованием оператора PIVOT. Для динамического создания новых столбцов таблицы используются значения вышеупомянутой строки @rows. Завершает все инструкция EXECUTE, в которую в качестве параметра передается строка @querry.

Использование

      Как правильно использовать созданную хранимую процедуру? Посмотрим на скриншот:

       Сценарий использования процедуры должен быть построен следующим образом:

1)    Для начала необходимо объявить переменную UDTT (User defined table type) табличного типа, о котором я писал выше – инструкция DECLARE.

2)    Затем - подготовить таблицу для передачи в хранимую процедуру (произвести инициализацию этой переменной, наполнив таблицу данными). За это отвечают два составных оператора INSERT…SELECT.

3)    После этого следует вызов хранимой процедуры с таблицей-параметром в операторе EXECUTE.

Ссылки

MSDN table-valued parameters - http://msdn.microsoft.com/en-us/library/bb510489.aspx

SQL.RU динамические запросы - http://www.sql.ru/faq/faq_topic.aspx?fid=104