Storing formula (equations) in database to be evaluated later (SQL Server 2005)

Question

I'm calculating linear regressions based on a data set. I do not know the regression model or number of parameters at compile-time.

I'm storing the regression equation in a SQL Server 2005 database as the string

y = 3x^2 + 2x // just an example

When I need to make a prediction, I grab the equation from the database, substitue x with the value I'm predicting, and use NCalc to evaluate the resulting string.

That method seems to work OK, but I'm wondering if there's a better way or a built-in feature of SQL Server that I've missed that would allow me to do these calculations on the database side.

Answer 1

You could write a CLR stored procedure that still uses NCalc to do the calculation.

Answer 2

I'd suggest putting it into a function along these lines. You can then call the function directly as well as having the ability to easily include the calculated value in view sets for reporting.

CREATE FUNCTION dbo.getRegression 
( @xvalue AS NUMERIC(18,2) --set the precision and scale as appropriate for your data
)
RETURNS NUMERIC(18,2)
    AS
    BEGIN
        DECLARE @yvalue as NUMERIC (18,2) 
        set @yvalue = POWER(2,(3*@xvalue)) + (2*@xvalue)
        RETURN @yvalue
    END
;