Solved

Dynamic SQL in function?

Posted on 2012-03-09
4
537 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 69

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 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

863 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

Need Help in Real-Time?

Connect with top rated Experts

26 Experts available now in Live!

Get 1:1 Help Now