I'm trying to create a function that uses linked server and openquery as a parameter in the query. As reference - this question started with this post:
So far, I've learned how to create a function (progress), but now it seems that parameters within functions is another case. I've made progress with a running dynamic SQL (more progress). Now, what to do with the dynamic SQL and get it into a function.
In the end, I'd like to write a query as:
select ID, fn_get_value(ID) from table1
-- where fn_get_value will perform this dynamic SQL:
DECLARE @variable VARCHAR(10)
DECLARE @sqlQuery VARCHAR(8000)
DECLARE @finalQuery VARCHAR(8000)
SET @variable = '123456'
SET @sqlQuery = ' SELECT entity_key FROM employee e WHERE e.empid = ' + '''' + '''' + @variable + '''' + ''''
SET @finalQuery = 'SELECT * FROM OPENQUERY(linkedserver,' + '''' + @sqlQuery + '''' + ')'
I've tried to turn this into a function by adding:
@outputvalue = EXEC(@finalQuery)
I'm at a roadblock - and most of the boards I've checked say this can't be done.