Functions in Functions

Hi,

I have to calculate data in function with "EXEC". During runtime I get the Error:

"Only functions and extended stored procedures can be executed from within a function."

I would use a Stored Procedure, but the function is to be called from a view. I don't understand, why that should not be possible. Is there any way to shut that message down or to work around?

btw: Storing all the data in a table, would mean a lot of work, I rather not like to do. ;-)

Thx for any help

Blubb10

Wih in the function,

- You can't use dynamic SQL

- You can't call any stored proc

These are the limitation of the Function.

Post your soruce code.

|||Here is a thread describe the issue similar to yours.

F.Y.I.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=457236&SiteID=1

Thanks,

Zuomin
|||Without knowing OP's logic we can't simply declare it is by design.. Let him post the logic used inside the function.. We will wait.. |||

DECLARE @.decBOM_Count INT

DECLARE @.strBSC_Formula NVARCHAR(200)

-- For demo, is a parameter of the function

SELECT @.strBSC_Formula = 'BR-(MW-25)-8'

--the next steps are, replacing all the variables in the formula by actual number-values

.

.

.

-- Here @.strBSC_Formula contains something like '1000-(70-25)-8'

SELECT @.strBSC_Formula = 'SET @.decBOM_Count =' + @.strBSC_Formula

EXEC sp_executesql @.strBSC_Formula, N'@.decBOM_Count DECIMAL(10, 4) OUTPUT', @.decBOM_Count OUTPUT

-- In @.decBOM_Count I expect a number as result of the formula.

|||

Ok..

Here you can't achive it from the function directly.

If you use SQL Server 2000,

You have to use extended stored procedure

- a com program which will evaluate the given fromula and return back the value

- need to register that com dll on the db server

If you use SQL Server 2005,

You have to use the CLR function

- a simple C#/VB.NET code which will evulate the expression.

Let me know the version of SQL Server.. I will try to help you on this.

|||

It is SQL Server 2005 Standard and the program is written in Access 2003 / VBA.

Function performances

I've made a user defined frunction that calculate a percentage reading data
from tables in db.
If i run the function with that code
set dateformat dmy
declare @.aaa DECIMAL(5,2)
set @.aaa=dbo.fn_name()
print @.aaa
or with that code
select dbo.fn_name()
it takes 21 seconds...too long
If i copy the code of the function and execute it in query analyzer, it
takes less than 1 second.
How is it possible?Function is not necessarily good for all kind of operation especially
for manipulating data in sets.
If your need to manipulate data in sets, you might as well do it in
stored proc to give you better performance and try to avoid cursor as
much as possible.
Roberto Lo Baido wrote:
> I've made a user defined frunction that calculate a percentage reading data
> from tables in db.
> If i run the function with that code
> set dateformat dmy
> declare @.aaa DECIMAL(5,2)
> set @.aaa=dbo.fn_name()
> print @.aaa
> or with that code
> select dbo.fn_name()
> it takes 21 seconds...too long
> If i copy the code of the function and execute it in query analyzer, it
> takes less than 1 second.
> How is it possible?

Function performances

I've made a user defined frunction that calculate a percentage reading data
from tables in db.
If i run the function with that code
set dateformat dmy
declare @.aaa DECIMAL(5,2)
set @.aaa=dbo.fn_name()
print @.aaa
or with that code
select dbo.fn_name()
it takes 21 seconds...too long
If i copy the code of the function and execute it in query analyzer, it
takes less than 1 second.
How is it possible?
Function is not necessarily good for all kind of operation especially
for manipulating data in sets.
If your need to manipulate data in sets, you might as well do it in
stored proc to give you better performance and try to avoid cursor as
much as possible.
Roberto Lo Baido wrote:
> I've made a user defined frunction that calculate a percentage reading data
> from tables in db.
> If i run the function with that code
> set dateformat dmy
> declare @.aaa DECIMAL(5,2)
> set @.aaa=dbo.fn_name()
> print @.aaa
> or with that code
> select dbo.fn_name()
> it takes 21 seconds...too long
> If i copy the code of the function and execute it in query analyzer, it
> takes less than 1 second.
> How is it possible?