?
Solved

Dynamic SQL in function?

Posted on 2012-03-09
4
Medium Priority
?
565 Views
Last Modified: 2012-03-10
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
0
Comment
Question by:kwieckii
  • 2
4 Comments
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 37703394
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
 

Author Comment

by:kwieckii
ID: 37704032
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
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 37704430
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
 

Author Closing Comment

by:kwieckii
ID: 37706040
Thanks for the info and link.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question