sp_executesql

[thomson]

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

[Dadou]

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

[Erland Sommarskog]

thomson (sa**********@y ahoo.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****@sommarsk og.se

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

[thomson]

Thank You Very Much For the Detailed Explanation

Regards

thomson

Erland Sommarskog wrote:

thomson (sa**********@y ahoo.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****@sommarsk og.se

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