Запуск функции с параметрами через openquery

#sql #sql_server #oracle

Есть у меня оракловский сервер на, котором есть некая функция, используемая примерно так:

select xxx, function(xxx) as yyy from oracle table


Еще есть MSSQL Server, связанный с оракловским через linked server+odbc, на котором
также нужно вызывать именно эту функцию, и непременно с самого оракла, а параметры
брать строго локально.

В прямом запросе через openquery отлично работает:

select * from openquery(ORACLE,'select function(somedata) from dual')


Но мне-то нужно передавать параметр somedata непосредственно из запроса на mssql,
так что делаю скалярную функцию-обертку с телом типа:

begin
select @result = result from openquery(ORACLE,'select function('+ @somedata +) as
result from dual)'
return result


и сталкиваюсь с тем, что openquery does not accept variables for its arguments.
Пробую через dynamic sql:

set @sql=select ...
exec @sql


и натыкаюсь на dynamic sql is not allowed in stored function or trigger.

Копаю дальше и делаю для функции еще одну обертку-процедуру с output-параметром:

declare @sql nvarchar(max), @params nvarchar(max),result numeric;
set @sql=n'select @resultOut = result from openquery(ORACLE,''select function('+@somedata+')
as result from dual'');';
set @params=n'@resultOut numeric output';
execute sp_executesql @sql, @params, @resultOut=@result output;


вызываю из открытого кода - отлично работает. Вызываю из функции - You cannot execute
a command with exec or sp_executesql nor can execute a stored procedure in a function.

Как мне передать параметры в openquery и вернуть оттуда результат непосредственно
из запроса на mssql server?
    

Ответы

Ответ 1

Подразумевается, что в MSSQL нужно передать данные, зная выборку для другой СУБД.
Для этого можно создать в ODBC алиас к другой СУБД, например odbc_alias и сделать SQL
запрос к удалённой (или локальной) субд.

create FUNCTION [dbo].[wsp_get_data]  ( @b char(13)  )
RETURNS 

  @tab TABLE  ( ev int,  fio varchar(255))
AS
BEGIN  
/*  sp_configure ‘show advanced options’, 1;  GO RECONFIGURE;
   sp_configure ‘Ole Automation Procedures’, 1; GO RECONFIGURE; + настройка контактной
зоны галочка */

declare @ado int; -- Переменные для ADO запроса
declare @db int;
declare @rs int;
declare @fields int;
declare @field int;
declare @hr int;
declare @eof int;
declare @a0  varchar(255) --  Переменные для таблицы
declare @a1 varchar(15)

set @sql =  'select  pid.recipient, pid.fio from tab1 where' + @b

EXEC @hr = sp_OACreate 'ADODB.Connection', @ado OUT, 1
if @hr=0 exec @hr = sp_OAMethod @ado, 'Open', NULL, 'odbc_alias'
if @hr=0 EXEC @hr = sp_OAMethod @ado, 'Execute', @rs OUT, @sql

set @eof = 1
if @hr=0 exec @hr = sp_OAMethod @rs, 'EOF', @eof out

declare @t int
set @t = 100

while @eof = 0 and @hr = 0 begin

   EXEC @hr = sp_OAMethod  @rs , 'fields', @fields out
   EXEC @hr = sp_OAMethod  @fields  , 'item',  @field out, 0; 
   EXEC @hr =   sp_OAMethod  @field  , 'value',   @a0 out; 
   exec sp_OADestroy @field
   EXEC @hr = sp_OAMethod  @fields  , 'item',  @field out, 1; 
   EXEC @hr = sp_OAMethod  @field  , 'value',   @a1 out; 
   exec sp_OADestroy @field
   exec sp_OADestroy @fields
   EXEC @hr = sp_OAMethod  @rs, 'MoveNext'
   insert into @tab values(@a0,@a1);
   set @eof = -1
   if @hr = 0 exec @hr = sp_OAMethod @rs, 'EOF', @eof out
   end

if @rs is not null  exec sp_OADestroy @rs
if @db is not null  exec sp_OADestroy @db
if @ado is not null exec sp_OADestroy @ado  
RETURN 
END