Saving formula (s) in a database to be evaluated later (SQL Server 2005)

Question

I am calculating linear regressions based on a dataset. I do not know the regression model or the number of parameters at compile time.

I save the regression equation in a SQL Server 2005 database as a string

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

When I need to make a prediction, I take the equation from the database, substitute x value that I predict, and use NCalc to evaluate the resulting string.

This method works fine, but I'm wondering if there is a better way or built-in SQL Server function that I skipped that would allow me to do these calculations on the database side .

Answer 1

You can write a CLR stored procedure that still uses NCalc to perform the calculation.

Answer 2

In Sql Server, something like this Select 2+2 will return 4. So you can have a stored procedure that reads a row from the database and then creates another dynamic row to call it (@SQLString) and run this query .

For example, in this case, the formula may be x + 2, then you create a dynamic row on it, and then call sp_executesql:

 EXEC sp_executesql @SQLString 

However, before you take this road, you should read this article about dynamic SQL .

I believe that you are doing it just fine.

Answer 3

I would suggest including a function in this function. Then you can directly call the function, and also be able to easily include the calculated values ​​in the report views.

 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 ; 

Answer 4

This is not an answer, but I do not have enough reputation for comment.

"You can write a CLR stored procedure that still uses NCalc to compute."

You CAN do this, but remember that you can only add links to Sql Server Projects, which can only link to other Sql server projects. Thus, you CAN create a SqlServer project and link all the files from the NCalc project and try to build it, but then you will have to do the same with all the links of the NCalc project. Not all of them are open source. I suppose you CAN use Reflector to decompile all of these links and put them in the SqlServer project.

But if you did all this and finally get your build solution, you will probably find out that you can add the link only as a UNSAFE link, which would mean that you have to start changing all kinds of SqlSever permissions ...

At this point, you will probably give up.

What I'm trying to say is much more work here than the original answer suggests.