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]

Re: sp_executesql

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:[color=blue]
> 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[/color]

[Erland Sommarskog]

Re: sp_executesql

thomson (saintthomson@y ahoo.com) writes:[color=blue]
> 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[/color]

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@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[thomson]

Re: sp_executesql

Thank You Very Much For the Detailed Explanation

Regards

thomson

Erland Sommarskog wrote:[color=blue]
> thomson (saintthomson@y ahoo.com) writes:[color=green]
> > Can sp_executesql used inside a user defined function, i
> > tried but it has compiled well, but when i call the functio it[/color][/color]
shows[color=blue][color=green]
> > Only functions and extended stored procedures can be executed from
> > within a function.
> >
> > What i have went wrong[/color]
>
> 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[/color]
affect[color=blue]
> the result?
>
> For this reason, you are not permitted to invoke anything from which[/color]
you[color=blue]
> theoretically can alter the database state, and that includes dynamic[/color]
SQL.[color=blue]
> (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@sommarsk og.se
>
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinf...2000/books.asp[/color]