I have a stored procedure which calls a lookup function as part of the SELECT statement:
-- PX type
CASE WHEN coi.SKU_ID like 'PX%' THEN
[GG].dbo.PXStockInventoryLookup(coi.SKU_ID)
ELSE
0
END
In the production environment, the GG database is on a different server so a linked server is used to reference it. Trying to call the function using 4 part naming gives this error:
Remote function reference 'UATLINK.GG.dbo.PXStockInventoryLookup' is not allowed, and the column name 'UATLINK' could not be found or is ambiguous.
After some research I found out about using Dynamic SQL, OPENQUERY and sp_executesql in a local function:
DECLARE @ParamDefinition as nvarchar(100)
SET @ParamDefinition = N'@Result int output'
DECLARE @sql nvarchar(4000);
SET @sql = 'SELECT * FROM OPENQUERY( [UATLINK], ''SELECT [GG].[dbo].[PXStockInventoryLookup](''''' + @param + ''''')'')'
exec sp_executesql @sql, @ParamDefinition, @Result = @sii output
return @sii
This code works fine in isolation, but I can't create it a function as SQL doesn't allow me to call a function from within another function. I can create is as a stored procedure but then I can't call it from the select statement!
I'm just going round in circles trying to solve this, any ideas?
