Varchar Operator to Dynamic Formula
Posted on 2013-02-04
I have a requirement for a user to enter two numbers into a db and an operator, TSQL would do its magic and then return a result. I have the code below, but the problem is that the operator is a varchar and building the formula is converting the output to a string - I need it to process the formula. Im thinking of sort sort of dyanamic SQL.
Below is my test code example - expected result is 200.
create TABLE ##TEST
INSERT INTO ##TEST VALUES ('100','+','100')
SELECT CONVERT(VARCHAR,VALUE1) + ' ' + OPERATOR + ' ' + CONVERT(VARCHAR,VALUE2) FROM
(SELECT VALUE1, CASE OPERATOR
WHEN '+' THEN '+'
END AS OPERATOR, VALUE2 FROM ##TEST) AS A
DROP TABLE ##TEST