Any help solidifying the following?

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 BOn flaw I see, but I am not sure how to fix is if the "nested" variables are more then 2 deep. What if I used Volume from above in a different formula?

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 * Z
1 | 1/2 Vol | v | V / 2

Now after executing the stored proc usp_BuildExpressions, the expression for 1/2 Vol would not work and end up looking like
A * 2 / 2.

It will stop short of replacing all the variables with the appropriated nested expressions.

Any ideas?

Mike B