thomson (saintthomson@yahoo.com) writes:
> Can sp_executesql used inside a user defined function, i
> tried but it has compiled well, but when i call the functio it shows
> Only functions and extended stored procedures can be executed from
> within a function.
>
> What i have went wrong

It's important to understand that user-defined functions are designed
from the presumption that they don't alter the state of the database.
Say that you have:

SELECT * FROM tbl WHERE col = dbo.udf()

And dbo.udf() would change the values in tbl.col. How would this affect
the result?

For this reason, you are not permitted to invoke anything from which you
theoretically can alter the database state, and that includes dynamic SQL.
(There are actually some loopholes, but obviously you would be doing
something unsupported and unpredictable if you tried it.)


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp