problem executing sp_executesql from a user-defined function

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

problem executing sp_executesql from a user-defined function

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-06-13 : 10:51:56

Al writes "Dear SQLTeam experts,
Here is my function:

CREATE FUNCTION my_func
(@tab varchar(30))
RETURNS int
AS
BEGIN
declare @retvalue as int
declare @temp_str varchar(100)

set @temp_str = 'select @retvalue = top 1 ind from ' + @tab

exec sp_executesql @temp_str

return @retvalue
END

When I try to execute the following statement in Query Analyzer:

select dbo.my_func('my_tab') ,

I get an error "Only functions and extended stored procedures can be executed from within a function." Even if I try to use the syntax like this:

exec master.dbo.sp_executesql @temp_str ,

I get the same error message. At the same time, Object Browser displays sp_executesql in the master/Extended Procedures folder.

What am I doing wrong? I am running MS SQL Server 2000.
Thank you in advance."

dsdeming

479 Posts

Posted - 2003-06-13 : 13:09:06

quote:
Only functions and extended stored procedures can be executed from within a function

You can't execute dynamic SQL in a udf. That means no EXECUTE and no sp_executesql. However, you're not doing anything in this code that couldn't be done with an ordinary stored procedure with an integer output parameter.

Dennis

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources