Solved

Dynamic SQL in function?

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SqlDataBase 7 46
sql server concatenate fields 10 31
Sql server get data from a usp to use in a usp 5 15
Help in Bulk Insert 9 30
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

786 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