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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Guy Hengel [angelIII / a3]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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
kwieckiiAuthor Commented:
Thanks for the info and link.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

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.