Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ggarman
Starting Member
2 Posts |
 Posted - 2007-05-16 : 08:23:02
|
| How do I make this work? I've read several ways to get a dynamic query to run in a OPENQUERY within a function, but this is the closest I've come. It will compile, but when I run it I get: "Only functions and extended stored procedures can be executed from within a function."All this is, is a simple lookup in a table, but it has turned into somewhat of a pain. I'm new to SQL, so please be nice. :)Thanks!CODE:CREATE FUNCTION GetNodeName (@PID varchar(100))RETURNS varchar(100)ASBEGIN DECLARE @TSQL varchar(100) DECLARE @ret varchar(100) SET @TSQL = 'SELECT * FROM OPENQUERY(EPODataProd,''SELECT NodeName FROM BranchNode WHERE AutoID = ''''' + @PID + ''''''')' exec @ret = sp_executesql @TSQL return @retENDGO |
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2007-05-16 : 08:32:44
|
| You can't use dynamic sql inside a user-defined function. Make use of SP instead.Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2007-05-16 : 10:22:08
|
| Also, as you dont pass object name as parameters, why do you want to do it in dynamic sql?MadhivananFailing to plan is Planning to fail |
|
|
|
ggarman
Starting Member
2 Posts |
Posted - 2007-05-17 : 10:47:54
|
| I'm trying to create a simple function similar to ones I created in Oracle. How else would I create a function like this without using dynamic SQL? What do you mean "you don't pass object name as parameters"? The table I'm querying basically has 3 columns, AutoID, NodeName and ParentID. The ParentIDs point to the AutoID to create a hierarchial structure. So, I'm wanting to query the table passing the ParentID in and getting that Node's Parent NodeName out.Thx! |
|
|
|
|
|
|
Help with OPENQUERY in Function