I am trying to create a table structure that would allow users to define custom formulas for computing 1 or more values for any given number of variables.Table StructureFORMULAID | Descr---------------1 | RectangleFORMULA_VARIABLEfkID | Dimension | Variable | Expression--------------------------------------1 | Side A | X | 1 | Size B | Y | 1 | Height | Z |1 | Area | A | X * Y1 | Volume | V | A * VFORMULA_VARIABLE_VALUEfkID | Variable | Value----------------------1 | X | 101 | Y | 101 | Z | 2
In the above, notice on FORMULA_VARIABLE the field named expression. If this is NULL, this will be a parameter that must be specified by the user, else, this value from the expression must be evaluated using the variable values.Notice the Volume expression uses the expression for the variable A. This is where the fun begins....I have produced the following using the code below. If you could please look at it and let me know if there are any ways to make this more effective, efficient, and stable. That would be greatly appreciated.The code is a first time run-through!USE NorthwindGOCREATE TABLE FORMULA( FormulaID int NOT NULL, Descr char(30))GOCREATE TABLE FORMULA_VARIABLE( fkFormulaID int NOT NULL, Dimension char(30), Variable char(10), Units char(5), Expression char(255))GOCREATE TABLE FORMULA_VARIABLE_VALUE( fkFormulaID int, Variable char(10), Value float)GOINSERT INTO FORMULA (FormulaID, Descr)SELECT 1, 'Rectangular'GOINSERT INTO FORMULA_VARIABLE (fkFormulaID, Dimension, Variable, Units, Expression)SELECT 1, 'Side 1', 'X', 'IN', NULL UNION ALLSELECT 1, 'Side 2', 'Y', 'IN', NULL UNION ALLSELECT 1, 'Height', 'Z', 'IN', NULL UNION ALLSELECT 1, 'Area', 'A', 'SI', 'X * Y' UNION ALLSELECT 1, 'Volume', 'V', 'I3', 'A * Z'GOGOINSERT INTO FORMULA_VARIABLE_VALUE (fkFormulaID, Variable, Value)SELECT 1, 'X', 10 UNION ALLSELECT 1, 'Y', 10 UNION ALLSELECT 1, 'Z', 2GOCREATE PROCEDURE usp_BuildExpressions @iFormula intASCREATE TABLE #TempFormulaResults( Dimension char(30), Value float)DECLARE @cDimension char(30), @cOldExp varchar(255), @cNewExp varchar(4000), @cVariable char(5)DECLARE OldExpCursor CURSOR FOR SELECT Dimension, Expression FROM FORMULA_VARIABLE WHERE fkFormulaID = 1 AND Expression IS NOT NULLOPEN OldExpCursorFETCH NEXT FROM OldExpCursor INTO @cDimension, @cOldExpWHILE (@@FETCH_STATUS = 0) BEGIN -- Iterate through expresions, build new cursor where dimension is not equal -- Replace expression variable with expressions DECLARE NewExpCursor CURSOR FOR SELECT Variable, Expression FROM FORMULA_VARIABLE WHERE fkFormulaID = @iFormula AND Expression IS NOT NULL AND NOT (Dimension = @cDimension) OPEN NewExpCursor FETCH NEXT FROM NewExpCursor INTO @cVariable, @cNewExp WHILE (@@FETCH_STATUS = 0) BEGIN SELECT @cOldExp = REPLACE(RTRIM(@cOldExp), RTRIM(@cVariable), RTRIM(@cNewExp)) FETCH NEXT FROM NewExpCursor INTO @cVariable, @cNewExp END CLOSE NewExpCursor DEALLOCATE NewExpCursor -- Get the variable values, replace values in expression and calcluate result DECLARE @fValue float, @cVarName char(5) DECLARE ValueCursor CURSOR FOR SELECT Variable, Value FROM FORMULA_VARIABLE_VALUE WHERE fkFormulaID = @iFormula OPEN ValueCursor FETCH NEXT FROM ValueCursor INTO @cVarName, @fValue WHILE(@@FETCH_STATUS = 0) BEGIN SELECT @cOldExp = REPLACE(@cOldExp, RTRIM(@cVarName), CONVERT(VARCHAR, @fValue)) FETCH NEXT FROM ValueCursor INTO @cVarName, @fValue END DECLARE @cSelect nvarchar(4000), @param nvarchar(4000), @Eval float SET @cSelect = 'SET @fResult = ' + @cOldExp SET @Param = '@fResult float OUTPUT' EXEC sp_executesql @cSelect, @Param, @Eval OUT INSERT INTO #TempFormulaResults (Dimension, Value) VALUES (@cDimension, @Eval) FETCH NEXT FROM OldExpCursor INTO @cDimension, @cOldExp CLOSE ValueCursor DEALLOCATE ValueCursor ENDCLOSE OldExpCursorDEALLOCATE OldExpCursorSELECT * FROM #TempFormulaResultsDROP TABLE #TempFormulaResultsGOEXEC usp_BuildExpressions 1DROP TABLE FORMULAGODROP TABLE FORMULA_VARIABLEGODROP TABLE FORMULA_VARIABLE_VALUEGODROP PROCEDURE usp_BuildExpressions
Any thoughts?Mike B