Search This Blog

Thursday, May 1, 2008

Invalid use of 'EXECUTE' within a function

I've tried to create a MSSQL "User Defined Functions" with execution of dynamic query (return a final value) and hit error message: "Invalid use of 'EXECUTE' within a function"

The reason is MSSQL not allow execution of dynamic query in "User Defined Functions". Because of this, i need to change from user "User Defined Functions" to "stored procedure" SP by using sp_executesql.

Below show example the Stored Procedure that i use to overcome my problem.

I need my SP to return a calculated value.

CREATE PROCEDURE dbo.psp_GetOCPrice
(
@strSKUNo VARCHAR(50),
@BOMT NUMERIC(9,2),
@OtherCharges NUMERIC(9,2) OUTPUT
)
AS
BEGIN

DECLARE @Count INT,
@Max INT,
@Cost NUMERIC(9,2),
@SQL NVARCHAR(4000),
@Formula NVARCHAR(1000),
@othMainIdn INT,
@othIdn INT


DECLARE @tblOthCharges TABLE
(
ID INT IDENTITY(1,1),
SKUNo VARCHAR(50),
othIdn INT,
othMainIdn INT,
Formula VARCHAR(1000),
Cost NUMERIC(9,2)
)

INSERT INTO @tblOthCharges(SKUNo, othIdn, othMainIdn, Formula)
SELECT sm.SKUNo, sm.pid, sm.ItemGroup,ISNULL(sm.Formula,0) Formula
FROM tblSKUMat sm
WHERE SKUNo = @strSKUNo
AND sm.othCharFlag = 1 ORDER BY sm.seqID ASC

SET @BOMT = ISNULL(@BOMT,0)

SET @Max = (SELECT COUNT(ID) FROM @tblOthCharges)

SET @Count = 1
SET @Cost = 0

SET @SQL = ''

SET @SQL = @SQL + ' DECLARE @BOM NUMERIC(9,2), ' + char(13)
SET @SQL = @SQL + ' @Cost NUMERIC(9,2) ' + char(13)
SET @SQL = @SQL + ' SET @BOM = ' + CONVERT(VARCHAR,@BOMT) + ' ' + char(13)
SET @SQL = @SQL + ' SET @rOtherCharges = 0 ' + char(13)

WHILE @Count <= @Max BEGIN SELECT @Formula = Formula, @othMainIdn = othMainIdn, @othIdn = othIdn FROM @tblOthCharges WHERE ID = @Count

SET @SQL = @SQL + ' SELECT @Cost = ' + CONVERT(VARCHAR,@Formula) + char(13)

SET @SQL = @SQL + ' DECLARE @v' + CONVERT(VARCHAR,@othMainIdn) + '_value NUMERIC(9,2) '


SET @SQL = @SQL
+ char(13)

SET @SQL = @SQL + ' SET @v' + CONVERT(VARCHAR,@othMainIdn) + '_value = ISNULL(@Cost,0) '

SET @SQL = @SQL
+ char(13)

SET @SQL = @SQL + ' SET @rOtherCharges = @rOtherCharges + ISNULL(@Cost,0) ' + char(13)

SET @Count = @Count + 1
END

DECLARE @ParmDefinition NVARCHAR(500);

SET @ParmDefinition = N'@rOtherCharges NUMERIC(9,2) OUTPUT';

EXECUTE sp_executesql
@SQL,
@ParmDefinition,
@rOtherCharges = @OtherCharges OUTPUT;

END

GO

i need to use dynamic query because the field "
Formula" is mathematics expression where involve calculation using value from parameter.
By using
sp_executesql, the value for @rOtherCharges will be assigned to @OtherCharges OUTPUT.

To call this SP, simply call execution

Declare
@OtherCharges
Execute dbo.psp_GetOCPrice 'XIN200805010001', 2000.20, @OtherCharges NUMERIC(9,2)

Done!



No comments: