SQL

SQL Server: переопределяем столбцы в результате хранимой процедуры

Время прочтения: 3 мин.

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

Иногда, при работе с данными выполнения хранимой процедуры SQL Server, может потребоваться изменить имена столбцов или тип данных столбцов, определяемых и возвращаемых в наборе результата этой хранимой процедуры, для использования этих изменений в другом параметре запроса или для более удобного отображения данных при просмотре таблицы (согласитесь, что не всегда название столбцов выглядят удобочитаемыми).

Предположим, хранимой процедурой возращен столбец с типом данных datetime, однако в дальнейшем потребуется использовать только часть даты (без времени) для этого столбца, иными словами, новый набор результатов должен иметь тип данных date. В этой статье я покажу как решить подобные задачи с применением параметра with result sets инструкцииexecute (в примерах использована общедоступная тестовая БД Northwind).

В начале выполним хранимую процедуру [Employee Sales by Country], для того чтобы получить «необработанные» результаты:

EXEC [Employee Sales by Country] @Beginning_Date = N'1998-01-01',
			     @Ending_Date = N'1998-12-31';

Результат следующий (фрагмент):

В зависимости от имеющихся требований к результату, можно, например, изменить тип данных столбца ShippedDate (исключить вывод времени, занимающего ненужное место и не представляющего интереса). Кроме того, можно вывести заголовки столбцов в более презентабельном виде, с пробелами, для лучшего восприятия при просмотре или изменить их названия.

Чтобы это сделать, используем with result sets:

EXEC [Employee Sales by Country] @Beginning_Date = N'1998-01-01',
			        @Ending_Date = N'1998-12-31'
WITH RESULT SETS   
(  
    (
        [Код страны] nvarchar(5),
        [Фамилия] nvarchar(40),
        [Имя ] nvarchar(20),
	[Дата доставки] date,
	[ID заказа] int,
	[Сумма продажи] decimal(19,2)
    )
);

Получаем новый результат (фрагмент):

Как видно из примера, изменены имена столбцов и тип данных.

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

Во-первых, давайте воспользуемся функцией sys.dm_exec_describe_first_result_set_for_object, для того чтобы получить информацию о том, какие фактические имена столбцов и типы данных, возвращаемые в результирующем наборе хранимой процедурой Employee Sales by Country.

SELECT 
    name,
    system_type_name,
    max_length,
    [precision],
    scale,
    user_type_name
FROM sys.dm_exec_describe_first_result_set_for_object(OBJECT_ID('Employee Sales by Country'), 0);

Результат вывода:

Столбцы ShippedDate и SaleAmount — это datetime и money соответственно.

В продолжение анализа воспользуемся функцией sys.dm_exec_describe_first_result_set, чтобы получить метаданные для измененного запроса:

SELECT 
    name,
    system_type_name,
    max_length,
    [precision],
    scale,
    user_type_name
FROM sys.dm_exec_describe_first_result_set(
    'EXEC [Employee Sales by Country]
WITH RESULT SETS   
(  
    (
        [Код страны] nvarchar(5),
        [Фамилия] nvarchar(40),
        [Имя] nvarchar(20),
        [Дата доставки] date,
        [ID заказа] int,
        [Сумма продажи] decimal(19,2)
    )
);', 
        NULL, 
        0
    );

В итоге:

Видно, что изменены названия и типы данных столбцов.

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

Используя приведенный выше способ можно быстро переопределить столбцы, возвращаемые в наборе результата хранимой процедуры так, чтобы в дальнейшем, например, было удобнее применять эти изменения в других запросах (подзапросах) или просто просматривать результирующую таблицу.

Советуем почитать