Need To Evaluate Stored String Formula

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Need To Evaluate Stored String Formula

Author

Topic

KevMull
Starting Member

11 Posts

Posted - 2007-06-18 : 06:48:48

In 'MyTable' I have the following columns...

TotalNumber (numeric)
Weighting (numreric)
Hours (numeric)
Minutes (numeric)
Formula (nvarchar)

'Formula' column stores a literal string of the formula that may include some, none or all of the previous columns or be NULL.

Here are some examples of the actual strings it stores...

Weighting*Hours
Weighting+(TotalNumber*Hours)
Weighting*Hours)+(TotalNumber*2)

etc etc

All I want to do is create a UDF that will evaluate these strings as math formula and return the value depending on the values of the other columns in the row.

Bear in mind that there may not be a string formula at all for some rows, in which case the value of teh Hours column alone should be the result.

I can do this in vb using the 'Replace' function but am having difficulty in translating it over to T-SQL.

Here is the vb version i use in Ms Access...

getFormula(strDutyType As String, dblTotalNumber As Double, dblWeightingAs Double, dblHours As Double, dblMinutes As String, strFormula As Variant)

If IsNull(strFormula) Or strFormula = "" Then
getFormula = dblHours
Exit Function
End If

'Create the expression string with literal values
strExpression = Replace(strFormula, "TotalNumber", _
CStr(dblTotalNumber), , , vbTextCompare) _
strExpression = Replace(strExpression, "Weighting", _
CStr(dblPF), , ,vbTextCompare)
strExpression = Replace(strExpression, "Hours", _
CStr(dblHours), , , vbTextCompare)
strExpression = Replace(strExpression, "Minutes", _
CStr(dblMinutes), , , vbTextCompare)

'Evaluate the math of the literal expression
getFormula = Eval(strExpression)

-----------

Many Thanks

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-06-18 : 07:34:25

well you can use EXEC() to execute dynamic sql (sql in strings) but that's about it.

you can't execute dynamic sql in user defined functions. only in stored procedures.
and no you can't call a stored procedure for every row in the table

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources