Sql-server – How to call a remote User Defined Function with Parameters

sql-server-2005

We have one SQL server which contains a link to another server that is on a different box. I would like to create a user-defined-function on ServerA which returns the value of the udf on ServerB, however SQL is telling me that Remote function calls are not allowed within a function.

Is there a way around this?

The udf does have a parameter, so I cannot use OPENQUERY. I thought I had it working by using sp_executesql, however although this works in a blank query window, it doesn't work from a user-defined function.

declare @someValue bit

exec ServerB.MyDatabase.dbo.sp_executesql 
    N'SELECT @someValue = dbo.[SomeUDF](@id)',
    N'@id int, @someValue bit OUTPUT', 
    @id=@id, @someValue=@someValue OUTPUT

select @someValue

The entire function gets called as part of a query, so I don't think I can use a stored procedure

SELECT *
FROM SomeTable
WHERE dbo.SomeUDF(Id) = 1

Best Answer

You can use OPENQUERY. You just have to use dynamic SQL to build the OPENQUERY call

DECLARE @sql nvarchar(4000);

SET @sql = 'SELECT * FROM OPENQUERY(linkedServerName, 
                          ''SELECT @someValue = dbo.[SomeUDF](' + CAST(@id etc) + ')'')';
EXEC (@sql)

However, you can't build this into a larger query on the local server.
UDFs + linked servers + parameters just don't play nice together...

Related Question