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

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

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

quote:

---

Originally posted by robvolk

I'd say that this is something that doesn't benefit from being in a database. SQL is not a mathematical language, and it's not good at expression substitution and evaluation. What I think would work better is to evaluate the expression outside of the database...using a separate program like VB, C#/C++, or Java/JavaScript. You can still store the expressions in the database and retrieve them when needed, but the actual calculations and presentation should be done separately.

---

quote:

---

Originally posted by robvolk

(DO NOT let end users create UDFs)

---