SQLTeam.com | Weblogs | Forums

Text field holds computed calculation and I need to convert it into my select statement

In the front end a user inputs how to configure the sell price of the part "AmtCost * 1.18". This is contained in a text field. How do I extract that out of the text to use in a select statement?

AmtCost is a field name. The user could use AmtPrice1(another Field name) or AmtCost2 or about 5 other cost fields.

So the users specifies which field he wants to use in the calculation then the (* or / or +) and then specifies the factor such as 1.18.

Not sure how to explain, I hope this make sense.

To do this you will need dynamic SQL - and since that is a 'text' field it is going to open you up to possible SQL injection attacks.

A better model might be a column that contains the 'field' to be used, a separate column for the operator - and a separate column for the factor. This would still require dynamic SQL - but you then can more control over what values can be entered by the user.

Either way - you will need dynamic SQL where you build of the SQL statement and then execute that statement.

Personally I'd add a trigger to validate and pre-parse the expression as it's inserted or updated in the table. The code that does the actual calc should not have to worry about validation.