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:
Post a Comment