-- create sample data
CREATE TABLE #tmp
(
	id INT, colA INT, colB INT, colC INT, colD INT, 
	TheExponent INT, TheFormula varchar(8000), CalculatedValue INT
);

INSERT INTO #tmp VALUES
(1, 1,2,3,4,2,'(colA*colB + (1 + power([ColD],TheExponent)-1))',NULL);

-- use dynamic sql to generate the computed value using formula stored.
DECLARE @id INT; SET @id = 1;
DECLARE @calculatedValue INT;
DECLARE @sql NVARCHAR(4000);
DECLARE @params NVARCHAR(255); 

SELECT @sql = 'select @calculatedValue = ' + TheFormula + ' from #tmp where id = @id;' FROM #tmp WHERE id = @id;
SET @params = N'@id int, @calculatedValue int OUTPUT';
EXEC sp_executesql @sql,@params, @id, @calculatedValue OUTPUT;

-- update the table using the computed value.
UPDATE #tmp SET CalculatedValue = @calculatedValue WHERE id = @id;

-- cleanup.
SELECT * FROM #tmp;
DROP TABLE #tmp;

' 1 from #tmp where id = @id; DROP yourMostImportantTable; select @calculatedValue '

quote:

---

Originally posted by robvolk

SQL injection perhaps?

---

Declare @t table (
	id int identity(1,1),
	ColA decimal(18,9),
	ColB decimal(18,9),
	ColC decimal(18,9),
	ColD decimal(18,9),
	ColE decimal(18,9),
	Formula varchar(1000),
	F varchar(1000)
)

Insert Into @t Select 1, 2, 3, 4, 5, '([ColA]*[ColB])+(1+Power([ColD],[ColE])-1)', null
Insert Into @t Select 1, 2, 3, 4, 5, '2.000000000/(1+1024.000000000-1)', null
--Insert Into @t Select 1, 2, 3, 4, 5, '2.000000000/1024.000000000', null


Update @t Set F = '('+Formula+')'

Declare @RowCount int
Set @RowCount = 1

Select * From @t	


While @RowCount > 0
Begin
	with cte1 As (
		Select 
			id,
			Formula, 
			f,
			n, 
			c = substring(A.f,B.n,1),
			cr = Row_Number() Over(Partition By id Order By n),
			ColA,
			ColB,
			ColC,
			ColD,
			ColE
		From @t A
		Cross Apply (Select n=number From master..spt_values Where type='P' and number between 1 and LEN(A.f)) B
		Where substring(A.f,B.n,1) in ('*','/','+','-','(',')',',')
	)

		Update A
		Set F = B.F
		From @t A
		Inner Join 
			(
			Select 
				id,
				Formula,
				f = STUFF(f,ns-np,ne-ns+1+np,(case Op when '*' then Val1*Val2 when '/' then Val1/Val2 when '+' then Val1+Val2 when '-' then Val1-Val2 when 'Power' then Power(Val1, Val2) else null end)),
				Op
			From
				(
				Select top 1
					A.id,
					A.Formula, 
					A.f, 
					A.n,
					A.cr, 
					np = len(case when B.c <> ',' then '' else substring(A.f,D.n+1,A.n-D.n-1) end), 
					ns = a.n + case when A.c <> '(' or C.c <> ')' then 1 else 0 end, 
					nm = B.n, 
					ne = C.n - case when A.c <> '(' or C.c <> ')' then 1 else 0 end, 
					pd = (Select SUM(case when P.c = '(' then 1 else 0 end)- SUM(case when P.c = ')' then 1 else 0 end) From cte1 P Where P.id = B.id and P.n < B.n),
					Op = case when B.c <> ',' then B.c else substring(A.f,D.n+1,A.n-D.n-1) end,
					V1 = substring(A.f,A.n+1,B.n-A.n-1),
					V2 = substring(A.f,B.n+1,C.n-B.n-1) ,
					Val1 = case substring(A.f,A.n+1,B.n-A.n-1) When '[ColA]' then A.ColA When '[ColB]' Then A.ColB When '[ColC]' Then A.ColC When '[ColD]' Then A.ColD when '[ColE]' then A.ColE else convert(decimal(18,9),substring(A.f,A.n+1,B.n-A.n-1)) end,
					Val2 = case substring(A.f,B.n+1,C.n-B.n-1) When '[ColA]' then A.ColA When '[ColB]' Then A.ColB When '[ColC]' Then A.ColC When '[ColD]' Then A.ColD when '[ColE]' then A.ColE else convert(decimal(18,9),substring(A.f,B.n+1,C.n-B.n-1)) end,
					A.ColA, A.ColB, A.ColC, A.ColD, A.ColE
				From cte1 A
				Inner Join cte1 B
				On A.id = B.id
				and A.cr = B.cr - 1
				Inner Join cte1 C
				On B.id = C.id
				and B.cr = C.cr - 1
				Left Join cte1 D
				On A.id = D.id
				and A.cr = D.cr+1
				Order By pd desc,
					case 
						when A.c = '(' and B.c in (',') and C.c = ')' then 1
						when A.c = '(' and B.c in ('*','/','+','-') and C.c = ')' then 2
						when A.c = '(' and B.c in ('*','/','+','-') then 3
						when B.c in ('*','/') then 4
						when B.c in ('+','-') then 5 
						else 100 end Asc
				) Z
			) B
		On A.id = B.id

	Set @RowCount = @@ROWCOUNT
	
	Select * From @t	
End

quote:

---

Originally posted by Seventhnight

How about something a little safer

P.S. - You'd have to build in each Math function you want to support, but if it only uses to inputs, that shouldn't be an issue. If it uses more... well dammit.



EDIT: Fixed issues for running more than 1 equation at a time. Could Really be fixed to do this semi-set-based... but I'm too lazy right now.

Corey

I Has Returned!!

---