How can I call a remote User Defined Function?

Question

I have SQL server which contains a link to another server(For e.g: test) I have a function in linked server(name:getvalue(@parameter)).so this function output i want to retrieve in SQL server 2012.I am using below methodology.

DECLARE @sql nvarchar(4000);
DECLARE @somevalue nvarchar(16)

SET @sql = 'SELECT * FROM OPENQUERY(test, 
                          ''SELECT @somevalue=getvalue(' +CAST('545454545' as nvarchar(10))+ ')'')';
EXEC (@sql)

I am getting error like

OLE DB provider "OraOLEDB.Oracle" for linked server "test" returned message "ORA-00936: missing expression". Msg 7321, Level 16, State 2, Line 1 An error occurred while preparing the query "SELECT @somevalue=getvalue(545454545)" for execution against OLE DB provider "OraOLEDB.Oracle" for linked server "test".

Please help me on this.Thanks in advance

Answer 1

Your remote query is nested twice, first in OPENQUERY, then in EXEC. The @somevalue variable is declared at the outermost level, it simply does not exist in the remote query's scope, so the reference is invalid.

But you also cannot use a variable there for two reasons:

1. First of all, as Colin 't Hart has noted, your linked server is Oracle. The SELECT @var = function(...) syntax is SQL Server and invalid in Oracle.

2. the OPENQUERY rowset function lets you treat the remote side's result only as a rowset – it is not designed to populate variables, whether along the way or as an alternative to the rowset. So, even if @var-like references were allowed in the underlying query (for instance, if the remote server was SQL Server), with OPENQUERY you would still be unable to reference outer ("this" side's) variables at the remote query's scope, nor would the outer scope be able to access variables declared at the inner scope ("that" side) in case you wanted to use them in your remote query.

To conclude at this point, the best way to get a result from an Oracle scalar function in SQL Server is probably to have your remote query return the result as a rowset while invoking the remote query using a rowset function like OPENQUERY. After you get the rowset from OPENQUERY, you can do with it whatever SQL Server allows you to do, and that includes storing its column values into variables. So, your OPENQUERY query could basically go like this:

SELECT
  @somevalue = somecolumn
FROM
  OPENQUERY(
    'test',
    'your Oracle query'
  )
;

Note the position of @somevalue = – it is moved one level higher, where it is now valid syntax and makes sense. The somecolumn would be the name that you would assign to your Oracle query's single column returning the result. This is probably how your Oracle query should look like:

SELECT getvalue(argument) AS somecolumn FROM DUAL

Now, if you did not need to parametrise argument, the complete Transact-SQL query could look like this:

DECLARE @somevalue nvarchar(16);

SELECT
  @somevalue = myresult
FROM
  OPENQUERY(
    test, 
    'SELECT getvalue(545454545) AS myresult FROM DUAL'
  )
;

But as you want to be able to parametrise the argument, you need to make the SELECT FROM OPENQUERY query dynamic: build it as a string:

DECLARE @sql nvarchar(max);

…

SET @sql =
N'SELECT
  …
FROM
  OPENQUERY(
    test, 
    ''SELECT getvalue(' + CAST(@someargument AS nvarchar(16)) + N') AS myresult FROM DUAL''
  )
';

and then execute the string:

EXEC (@sql);

All that you are already doing. However, when you use the EXEC (...) syntax, you cannot directly receive the result into a variable¹, you can only get it as a result set – yes, same limitation as with OPENQUERY. Unlike OPENQUERY, though, you cannot call EXEC in a FROM clause.

So, at this point you are still facing the problem of putting the result of getvalue into a variable.

Basically, there are two ways to go about it. First is to drop the @somevalue = bit from the dynamic query to let the OPENQUERY's result set pass through to the outermost scope. Although you cannot do ... FROM EXEC (...), you can do INSERT ... EXEC (...). So, insert the result set of EXEC into a temporary dataset (like a table variable):

DECLARE @tmpresult TABLE (result nvarchar(16));

INSERT INTO @tmpresult (result)
EXEC (@sql);

then read the column value from the temporary storage into a scalar variable:

SELECT @somevalue = result FROM @tmpresult;

The other, and, in my opinion, better, option is to use EXEC sp_executesql instead of EXEC () to execute the dynamic query. With the sp_executesql system stored procedure you can have parameters for the underlying query, including output parameters. An output parameter is precisely how you can pass the result into your @somevalue variable that you have declared in your main script.

So, keep the @somevalue = part of the dynamic query but declare it when invoking the query rather than in the query. That way the variable will work not just as a local variable but as a parameter. Including OUTPUT into the parameter declaration will make it an output parameter too. So, this is how you go:

SET @sql =
N'SELECT
  @tmpvalue = myresult
FROM
  …
';

EXEC sp_executesql @sql, '@tmpvalue nvarchar(16) OUTPUT', @somevalue OUTPUT;

You can see in the above code that I slightly changed the name of the variable in the dynamic query. I did that to emphasise the fact that the variable in your dynamic query and the one in your main script are different things. The @tmpvalue is the variable with which you work inside the dynamic query, while @somevalue is your main scope's variable that you will be passing as an actual receiver of the value stored in @tmpvalue. That is the way of exchanging data between variables of a dynamic query and those of the calling script. Note also that you must specify OUTPUT both in the parameter declaration and when specifying the receiver variable. This is the same convention as with output parameters of user-defined stored procedures.

So, to summarise, the final code would look something like this:

DECLARE
  @sql      nvarchar(max),
  @somvalue nvarchar(16)
;

SET @sql =
N'SELECT
  @tmpvalue = myresult
FROM
  OPENQUERY(
    test, 
    ''SELECT getvalue(' + CAST(@someargument AS nvarchar(16)) + N') AS myresult FROM DUAL''
  )
';

EXEC sp_executesql
  @sql,
  N'@tmpvalue nvarchar(16) OUTPUT',
  @somevalue OUTPUT
;

---

_{¹Actually you can have parameters, including output parameters, with EXEC, but only if you have specified to execute the dynamic query at a remote server:}

EXEC (@sql, @var1, @var2, ...) AT linked_server

_{Usually this is more useful when the remote server is a non-SQL Server one, as for SQL Server instances the EXEC sp_executesql method would likely be preferable.}

Answer 2

@somevalue does not exist in the context of the linked queries. It has been declared above in the main session. Exec or OpenQuery does not see it. Then Your EXEC query will just output the data from the main select. If you want to get the value back in your variable you can update @sql and use sp_executesql:

SET @Param = N'@returnvalue nvarchar(16) OUTPUT';
EXECUTE sp_executesql @sql, @Param, @returnValue = @someVariable Output;
SELECT @max_title;