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.