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 Structure

FORMULA

ID | Descr

---------------

1 | Rectangle

FORMULA_VARIABLE

fkID | Dimension | Variable | Expression

--------------------------------------

1 | Side A | X |

1 | Size B | Y |

1 | Height | Z |

1 | Area | A | X * Y

1 | Volume | V | A * V

FORMULA_VARIABLE_VALUE

fkID | Variable | Value

----------------------

1 | X | 10

1 | Y | 10

1 | 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 Northwind

GO

CREATE TABLE FORMULA

(

FormulaID int NOT NULL,

Descr char(30)

)

GO

CREATE TABLE FORMULA_VARIABLE

(

fkFormulaID int NOT NULL,

Dimension char(30),

Variable char(10),

Units char(5),

Expression char(255)

)

GO

CREATE TABLE FORMULA_VARIABLE_VALUE

(

fkFormulaID int,

Variable char(10),

Value float

)

GO

INSERT INTO FORMULA (FormulaID, Descr)

SELECT 1, 'Rectangular'

GO

INSERT INTO FORMULA_VARIABLE (fkFormulaID, Dimension, Variable, Units, Expression)

SELECT 1, 'Side 1', 'X', 'IN', NULL UNION ALL

SELECT 1, 'Side 2', 'Y', 'IN', NULL UNION ALL

SELECT 1, 'Height', 'Z', 'IN', NULL UNION ALL

SELECT 1, 'Area', 'A', 'SI', 'X * Y' UNION ALL

SELECT 1, 'Volume', 'V', 'I3', 'A * Z'GO

GO

INSERT INTO FORMULA_VARIABLE_VALUE (fkFormulaID, Variable, Value)

SELECT 1, 'X', 10 UNION ALL

SELECT 1, 'Y', 10 UNION ALL

SELECT 1, 'Z', 2

GO

CREATE PROCEDURE usp_BuildExpressions

@iFormula int

AS

CREATE 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 NULL

OPEN OldExpCursor

FETCH NEXT FROM OldExpCursor INTO @cDimension, @cOldExp

WHILE (@@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

END

CLOSE OldExpCursor

DEALLOCATE OldExpCursor

SELECT * FROM #TempFormulaResults

DROP TABLE #TempFormulaResults

GO

EXEC usp_BuildExpressions 1

DROP TABLE FORMULA

GO

DROP TABLE FORMULA_VARIABLE

GO

DROP TABLE FORMULA_VARIABLE_VALUE

GO

DROP PROCEDURE usp_BuildExpressions

Any thoughts?

Mike B