By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,413 Members | 1,594 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,413 IT Pros & Developers. It's quick & easy.

sp_executesql

P: n/a
Hi all,
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

Thanks in advance

thomson

Jul 23 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Unfortunately, it means exactly what it sais - that you cannot execute
anything from within a UDF except an *extended* stored procedure or
another UDF.

Since sp_executesql is not an extended stored procedure it will not
work. You will need to execute your sp_executesql statement within a
stored procedure instead. Good luck!

Dadou.

thomson wrote:
Hi all,
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

Thanks in advance

thomson


Jul 23 '05 #2

P: n/a
thomson (sa**********@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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #3

P: n/a
Thank You Very Much For the Detailed Explanation

Regards

thomson

Erland Sommarskog wrote:
thomson (sa**********@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, es****@sommarskog.se

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


Jul 23 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.