Трюки с внешними источниками данных

CREATE TABLE [dbo].[KeyOperations](
	[ID] [binary](16) NOT NULL,
	[Name] [nvarchar](150) NOT NULL,
	[Description] [nvarchar](max) NULL,
    CONSTRAINT [PK_KeyOperations] PRIMARY KEY CLUSTERED 
    (
        [ID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[PerformanceMeasurements](
	[KeyOperation] [varchar](34) NOT NULL,
	[BeginDate] [numeric](15, 0) NOT NULL,
	[SessionID] [numeric](15, 0) NOT NULL,
	[PeriodHour] [datetime2](7) NOT NULL,
	[Duration] [numeric](15, 3) NOT NULL,
	[UserName] [nvarchar](150) NOT NULL,
    CONSTRAINT [PK_PerformanceMeasurements] PRIMARY KEY CLUSTERED 
    (
        [KeyOperation] ASC,
        [BeginDate] ASC,
        [SessionID] ASC,
        [PeriodHour] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE PROCEDURE [dbo].[ExecCustomCommand]
	@SqlCommand nvarchar(max)
AS
BEGIN	
	EXEC sp_executesql @SqlCommand;
END
GO

CREATE PROCEDURE [dbo].[ProcWithOutputParams]
	@inputParam INT,
	@outputParam int = NULL OUTPUT,
	@outputParamOther int = NULL OUTPUT
AS
BEGIN
	SET @outputParam = @inputParam + 10;
	SET @outputParamOther = @outputParam * 100;
END
GO

Запрос получения информации об активных соединениях, а также тексты SQL-запросов для них. 

SELECT   
	s.session_id, 
    r.status, 
    r.cpu_time, 
    Substring(st.TEXT,(r.statement_start_offset / 2) + 1, 
                    ((CASE r.statement_end_offset 
                        WHEN -1 
                        THEN Datalength(st.TEXT) 
                        ELSE r.statement_end_offset 
                        END - r.statement_start_offset) / 2) + 1) AS statement_text, 
	r.command, 
    s.login_name, 
    s.host_name, 
    s.program_name
FROM sys.dm_exec_sessions AS s 
	JOIN sys.dm_exec_requests AS r 
		ON r.session_id = s.session_id 
	CROSS APPLY sys.Dm_exec_sql_text(r.sql_handle) AS st 

Для этих целей используются несколько системных таблиц (DMV).

Маленькая процедура для SQL Server.

CREATE PROCEDURE [dbo].[ExecCustomCommand]
	@SqlCommand nvarchar(max)
AS
BEGIN	
	EXEC sp_executesql @SqlCommand;
END

Но большая процедура для внешних источников данных!

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

-- Имя глобальной временной таблицы начинается с ##.
-- Будем считать, что у нас только 1 временная таблица для целей передачи данных,
-- хотя можно было бы усложнить пример и создавать свои временные таблицы
-- для каждой сессии
IF(OBJECT_ID('tempdb..##CallsAndValues_ExternalDataSource') IS NULL)
BEGIN
	BEGIN TRY 
		CREATE TABLE ##CallsAndValues_ExternalDataSource
		(
			-- Поле идентификатора вызова (какой-то GUID)
			CallId varchar(36) NOT NULL,
			-- Результат в виде текста
			Result nvarchar(max) NULL
		)
		-- Создаем индекс для оптимизации поиска
		CREATE UNIQUE CLUSTERED INDEX [_indx1] ON ##CallsAndValues_ExternalDataSource
		(
			[CallId] ASC
		)
	END TRY 
	BEGIN CATCH 
		-- При ошибке ничего не делаем
	END CATCH  
END

Костыль, не отрицаю!

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

	// Инициализируем идентификатор вызова
	ИдентификаторВызова = Новый УникальныйИдентификатор;
	ИдентификаторВызова = Строка(ИдентификаторВызова);
	ПараметрХранимойПроцедуры = 100;
	
	// Инициализация промежуточной глобальной временной таблицы при необходимости
	КомандаИнициализацииПромежуточнойТаблицы = "
	|IF(OBJECT_ID('tempdb..##CallsAndValues_ExternalDataSource') IS NULL)
	|BEGIN
	|	BEGIN TRY 
	|		CREATE TABLE ##CallsAndValues_ExternalDataSource
	|		(
	|			-- Поле идентификатора вызова (какой-то GUID)
	|			CallId varchar(36) NOT NULL,
	|			-- Результат в виде текста
	|			Result nvarchar(max) NULL
	|		)
	|		-- Создаем индекс для оптимизации поиска
	|		CREATE UNIQUE CLUSTERED INDEX [_indx1] ON ##CallsAndValues_ExternalDataSource
	|		(
	|			[CallId] ASC
	|		)
	|	END TRY 
	|	BEGIN CATCH 
	|		-- При ошибке ничего не делаем
	|	END CATCH  
	|END
	|";
	ВнешниеИсточникиДанных.ПримерИсточникаДанных.ExecCustomCommand(КомандаИнициализацииПромежуточнойТаблицы);
	
	// Формируем SQL-команду для вызова хранимой процедуры и сохранения
	// параметров в промежуточную временную таблицу
	КомандаSQL = "
	|SET NOCOUNT ON;
	|
	|DECLARE	@outputParam int,
	|		@outputParamOther int,
	|		@callId nvarchar(36) = '&ИдентификаторВызова'
	|
	|EXEC	[dbo].[ProcWithOutputParams]
	|		@inputParam = &ПараметрХранимойПроцедуры,
	|		@outputParam = @outputParam OUTPUT,
	|		@outputParamOther = @outputParamOther OUTPUT
	|
	|DECLARE @xmlResult xml =
	|(
	|	SELECT	@outputParam as N'outputParam',
	|			@outputParamOther as N'outputParamOther'
	|	FOR XML RAW
	|)
	|
	|DELETE ##CallsAndValues_ExternalDataSource
	|WHERE CallId = @callId;
	|
	|INSERT ##CallsAndValues_ExternalDataSource (CallId, Result)
	|	VALUES(@callId, CAST(@xmlResult AS nvarchar(max)))
	|";	
	КомандаSQL = СтрЗаменить(КомандаSQL, "&ИдентификаторВызова", ИдентификаторВызова);	
	КомандаSQL = СтрЗаменить(КомандаSQL, "&ПараметрХранимойПроцедуры", XMLСтрока(ПараметрХранимойПроцедуры));	
	ВнешниеИсточникиДанных.ПримерИсточникаДанных.ExecCustomCommand(КомандаSQL);
	
	// Получаем результат выполнения в виде строки XML
	Запрос = Новый Запрос;
	Запрос.Текст = 
		"ВЫБРАТЬ
		|	CallsAndValues.Result КАК Result
		|ИЗ
		|	ВнешнийИсточникДанных.ПримерИсточникаДанных.Таблица.CallsAndValues КАК CallsAndValues
		|ГДЕ
		|	CallsAndValues.ID = &ID";	
	Запрос.УстановитьПараметр("ID", ИдентификаторВызова);	
	РезультатЗапроса = Запрос.Выполнить();	
	Выборка = РезультатЗапроса.Выбрать();
	Выборка.Следующий();	
	РезультатXML = Выборка.Result;
	
	// Удаляем результат из промежуточной таблицы
	ТекстКомандыОчисткиПолученногоЗначения = "
	|DECLARE @sql nvarchar(max) = 'DELETE FROM ##CallsAndValues_ExternalDataSource WHERE CallId = @CallId';
	|EXECUTE sp_executesql @sql, N'@CallId varchar(36)', @CallId = '" + ИдентификаторВызова + "'
	|";
	ВнешниеИсточникиДанных.ПримерИсточникаДанных.ExecCustomCommand(ТекстКомандыОчисткиПолученногоЗначения);
	
	// Разбираем полученный XML
	Парсер = Новый ЧтениеXML;
    Парсер.УстановитьСтроку(РезультатXML);
	Построитель = Новый ПостроительDOM; 
    Документ = Построитель.Прочитать(Парсер);
	раз = новый РазыменовательПространствИменDOM(Документ, "");
	РезультатПоиска = Документ.ВычислитьВыражениеXPath("/row", Документ, раз);
	НайденныйЭлемент = РезультатПоиска.ПолучитьСледующий();
	outputParam = НайденныйЭлемент.Атрибуты.ПолучитьИменованныйЭлемент("outputParam").Значение;
	outputParamOther = НайденныйЭлемент.Атрибуты.ПолучитьИменованныйЭлемент("outputParamOther").Значение;

	// Выводим исходную XML и полученные значения
	Сообщить("XML: " + РезультатXML);
	Сообщить("outputParam: " + outputParam);
	Сообщить("outputParamOther: " + outputParamOther);

Общий принцип работы алгоритма следующий:

1. Инициализируем идентификатор вызова, по которому потом можно будет получить результат. Также подготавливаем все необходимые параметры для процедуры (в нашем случае это один входной параметр "@inputParam" с типом INT).
2. Проводим инициализацию глобальной временной таблицы, если она еще не создана.
3. Выполняем SQL-команду для вызова хранимой процедуры с последующим преобразованием результата в XML, и сохранением в промежуточную таблицу.
4. Получаем значение XML из промежуточной таблицы и удаляем из нее полученное значение.
5. Выводим результат.

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

SET NOCOUNT ON;

-- Инициализация необходимых переменных
DECLARE	@outputParam int,
		@outputParamOther int,
		@callId nvarchar(36) = '&ИдентификаторВызова'

-- Вызов процедуры
EXEC	[dbo].[ProcWithOutputParams]
		@inputParam = &ПараметрХранимойПроцедуры,
		@outputParam = @outputParam OUTPUT,
		@outputParamOther = @outputParamOther OUTPUT

-- Выбираем выходные параметры с помощью обычного SELECT,
-- преобразовывая результат к XML с помощью выражения "FOR XML RAW"
DECLARE @xmlResult xml =
(
	SELECT	@outputParam as N'outputParam',
			@outputParamOther as N'outputParamOther'
	FOR XML RAW
)

-- Очищаем промежуточную таблицу от значений
-- с таким же идентификатором вызова
DELETE ##CallsAndValues_ExternalDataSource
WHERE CallId = @callId;

-- Добавляем XML в промежуточную таблицу, предварительно
-- преобразовав ее к строке
INSERT ##CallsAndValues_ExternalDataSource (CallId, Result)
	VALUES(@callId, CAST(@xmlResult AS nvarchar(max)))

Как итог, на стороне 1С у нас есть текст XML, который мы разобрали и получили выходные параметры хранимой процедуры. Остается только продолжить с ними работу (преобразовать к нужному типу, сохранить куда-либо и т.д.).

 В общем модуле оставляем только один экспортный метод "ВыполнитьПроизвольныйСкрипт", остальное только для служебного использования.

#Область ПрограммныйИнтерфейс

Функция ВыполнитьПроизвольныйСкрипт(Знач ТекстСкрипта) Экспорт
	
	ИнициализацияПромежуточнойТаблицы();
	
	ИдентификаторВызова = Новый УникальныйИдентификатор;
	ИдентификаторВызова = Строка(ИдентификаторВызова);
	
	ТекстСкрипта = СтрЗаменить(ТекстСкрипта, "&ИдентификаторВызова", ИдентификаторВызова);
	
	ВнешниеИсточникиДанных.ПримерИсточникаДанных.ExecCustomCommand(ТекстСкрипта);	
	
	Возврат ПолучитьРезультатВызова(ИдентификаторВызова, Истина);
	
КонецФункции

#КонецОбласти

#Область Служебный

Процедура ИнициализацияПромежуточнойТаблицы()
	
	КомандаИнициализацииПромежуточнойТаблицы = "
	|IF(OBJECT_ID('tempdb..##CallsAndValues_ExternalDataSource') IS NULL)
	|BEGIN
	|	BEGIN TRY 
	|		CREATE TABLE ##CallsAndValues_ExternalDataSource
	|		(
	|			-- Поле идентификатора вызова (какой-то GUID)
	|			CallId varchar(36) NOT NULL,
	|			-- Результат в виде текста
	|			Result nvarchar(max) NULL
	|		)
	|		-- Создаем индекс для оптимизации поиска
	|		CREATE UNIQUE CLUSTERED INDEX [_indx1] ON ##CallsAndValues_ExternalDataSource
	|		(
	|			[CallId] ASC
	|		)
	|	END TRY 
	|	BEGIN CATCH 
	|		-- При ошибке ничего не делаем
	|	END CATCH  
	|END
	|";
	
	ВнешниеИсточникиДанных.ПримерИсточникаДанных.ExecCustomCommand(КомандаИнициализацииПромежуточнойТаблицы);	
	
КонецПроцедуры

Функция ПолучитьРезультатВызова(ИдентификаторВызова, УдалитьЗначениеПослеПолучения = Ложь)
	
	Запрос = Новый Запрос;
	Запрос.Текст = 
		"ВЫБРАТЬ
		|	CallsAndValues.Result КАК Result
		|ИЗ
		|	ВнешнийИсточникДанных.ПримерИсточникаДанных.Таблица.CallsAndValues КАК CallsAndValues
		|ГДЕ
		|	CallsAndValues.ID = &ID";	
	Запрос.УстановитьПараметр("ID", ИдентификаторВызова);	
	РезультатЗапроса = Запрос.Выполнить();	
	Выборка = РезультатЗапроса.Выбрать();
	
	Если УдалитьЗначениеПослеПолучения Тогда
		УдалитьЗначениеВызова(ИдентификаторВызова);
	КонецЕсли;
	
	Если Выборка.Следующий() Тогда
		Возврат Выборка.Result;
	Иначе
		Возврат Null;
	КонецЕсли;
	
КонецФункции

Процедура УдалитьЗначениеВызова(ИдентификаторВызова)
	
	ТекстКоманды = "
	|DECLARE @sql nvarchar(max) = 'DELETE FROM ##CallsAndValues_ExternalDataSource WHERE CallId = @CallId';
	|EXECUTE sp_executesql @sql, N'@CallId varchar(36)', @CallId = '" + ИдентификаторВызова + "'
	|";
	
	ВнешниеИсточникиДанных.ПримерИсточникаДанных.ExecCustomCommand(ТекстКоманды);
	
КонецПроцедуры

#КонецОбласти

В процедуру передается текст SQL-команды для выполнения, при этом в скрипте должен быть указан параметр "&ИдентификаторВызова", чтобы его можно было явно подменить перед выполнением. Если такого параметра в скрипте нет, то получить возвращаемое значение будет нельзя.

Алгоритмы можно еще улучшить, но для примера этого должно быть достаточно.

Теперь вызов хранимой процедуры выглядит проще.

	ПараметрХранимойПроцедуры = 100;
	
	КомандаSQL = "
	|SET NOCOUNT ON;
	|
	|DECLARE	@outputParam int,
	|		@outputParamOther int,
	|		@callId nvarchar(36) = '&ИдентификаторВызова'
	|
	|EXEC	[dbo].[ProcWithOutputParams]
	|		@inputParam = &ПараметрХранимойПроцедуры,
	|		@outputParam = @outputParam OUTPUT,
	|		@outputParamOther = @outputParamOther OUTPUT
	|
	|DECLARE @xmlResult xml =
	|(
	|	SELECT	@outputParam as N'outputParam',
	|			@outputParamOther as N'outputParamOther'
	|	FOR XML RAW
	|)
	|
	|DELETE ##CallsAndValues_ExternalDataSource
	|WHERE CallId = @callId;
	|
	|INSERT ##CallsAndValues_ExternalDataSource (CallId, Result)
	|	VALUES(@callId, CAST(@xmlResult AS nvarchar(max)))
	|";		
	КомандаSQL = СтрЗаменить(КомандаSQL, "&ПараметрХранимойПроцедуры", XMLСтрока(ПараметрХранимойПроцедуры));
	
	РезультатXML = ПомощникРаботыСВнешнимИсточникомДанных.ВыполнитьПроизвольныйСкрипт(КомандаSQL);
	
	// Разбираем полученный XML
	Парсер = Новый ЧтениеXML;
    Парсер.УстановитьСтроку(РезультатXML);
	Построитель = Новый ПостроительDOM; 
    Документ = Построитель.Прочитать(Парсер);
	раз = новый РазыменовательПространствИменDOM(Документ, "");
	РезультатПоиска = Документ.ВычислитьВыражениеXPath("/row", Документ, раз);
	НайденныйЭлемент = РезультатПоиска.ПолучитьСледующий();
	outputParam = НайденныйЭлемент.Атрибуты.ПолучитьИменованныйЭлемент("outputParam").Значение;
	outputParamOther = НайденныйЭлемент.Атрибуты.ПолучитьИменованныйЭлемент("outputParamOther").Значение;

	// Выводим исходную XML и полученные значения
	Сообщить("XML: " + РезультатXML);
	Сообщить("outputParam: " + outputParam);
	Сообщить("outputParamOther: " + outputParamOther);

Но работать через ADO все равно будет эффективней!

Самое важное - это передача результатирующего набора процедуры в табличную переменную, который потом можно преобразовать к XML. 

DECLARE @callId nvarchar(36) = '&ИдентификаторВызова'

-- Создаем переменную с типом таблица
DECLARE @T Table (
	spid INT NULL, 
	ecid INT NULL, 
	status nvarchar(50) NULL, 
	loginname nvarchar(50) NULL, 
	hostname nvarchar(50) NULL, 
	blk INT NULL, 
	dbname nvarchar(50) NULL, 
	cmd nvarchar(50) NULL, 
	request_id INT null
);
-- Вызываем процедуру и сохраняем первый возвращаемый набор в таблицу
INSERT @T EXEC sp_who;

-- Сохраняем набор записей в XML
DECLARE @xmlResult xml =
(
	SELECT * FROM @T
	FOR XML RAW
)

DELETE ##CallsAndValues_ExternalDataSource
WHERE CallId = @callId;

INSERT ##CallsAndValues_ExternalDataSource (CallId, Result)
	VALUES(@callId, CAST(@xmlResult AS nvarchar(max)))

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

 Тут ничего нового не увидите, все аналогично предыдущему примеру.

	КомандаSQL = "
	|DECLARE @callId nvarchar(36) = '&ИдентификаторВызова'
	|
	|-- Создаем переменную с типом таблица
	|DECLARE @T Table (
	|	spid INT NULL, 
	|	ecid INT NULL, 
	|	status nvarchar(50) NULL, 
	|	loginname nvarchar(50) NULL, 
	|	hostname nvarchar(50) NULL, 
	|	blk INT NULL, 
	|	dbname nvarchar(50) NULL, 
	|	cmd nvarchar(50) NULL, 
	|	request_id INT null
	|);
	|-- Вызываем процедуру и сохраняем первый возвращаемый набор в таблицу
	|INSERT @T EXEC sp_who;
	|
	|-- Сохраняем набор записей в XML
	|DECLARE @xmlResult xml =
	|(
	|	SELECT * FROM @T
	|	FOR XML RAW
	|)
	|
	|DELETE ##CallsAndValues_ExternalDataSource
	|WHERE CallId = @callId;
	|
	|INSERT ##CallsAndValues_ExternalDataSource (CallId, Result)
	|	VALUES(@callId, CAST(@xmlResult AS nvarchar(max)))";
		
	РезультатXML = ПомощникРаботыСВнешнимИсточникомДанных.ВыполнитьПроизвольныйСкрипт(КомандаSQL);
	
	// Далее необходимо разобрать XML

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