Dynamic SQL in function?

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.

Who is Participating?
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
sorry, a function cannot have dynamic sql.

for your information, this article will help you to workaround for most cases:
Scott PletcherSenior DBACommented:
You could go thru some gyrations to get a function to work, but it would be much easier and vastly more efficient to just do a join in the query itself:

select ID, e.entity_key
from table1 t1
left outer join linkedserver.dbname.dbo.employee e ON
    e.empid = t1.ID
kwieckiiAuthor Commented:
The SQL that I need to get from the linked server is a bit more complicated than the example.  Also- I'd like to figure out how to do it.  However, I did try something like what you suggesged - and it didn't work as I'd hoped. btw -my linked server is an Oracle db.
kwieckiiAuthor Commented:
Thanks for the info and link.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.