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:
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_27623954.html


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 + '''' + ')'
EXEC(@finalQuery)


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.

Thanks
kwieckiiAsked:
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:
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/A_6093-Running-queries-on-remote-databases-from-SQL-Server.html
0
 
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
0
 
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.
0
 
kwieckiiAuthor Commented:
Thanks for the info and link.
0
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.