Creating dynamic queries in user defined functions

[fractorr]

I am trying to create a function that builds a dynamic query that accepts a table name and field names that goes through a table that is a category table and finds the first parent category record for any given record.

I am using SP_EXECUTESQL int he fucntion and keep getting the error:

Server: Msg 557, Level 16, State 2, Procedure fGetRecursiveMaster, Line 7
Only functions and extended stored procedures can be executed from within a function.

Is there anyway around this? My code works great in as a stored procedure but I need it as a function.

Any ideas?

 

[nigelrivett]

You can try using openquery or openrowset but I wouldn't advise it. UDF's are not meant to do this sort of thing and you can cause processing errors if you circumvent the checking.
Better to redesign what you are trying to do.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.