Posted by Erland Sommarskog on 10/01/20 11:18

Bkr (keepitliteus@yahoo.com) writes:
> Here is the business reason that prompts me to do this. Business would
> like to compute a set of say 50 values for about 30 different items. If
> I have to hardcode the calculations it will be 50x30 = 1500 items.
>
> Also, if they had to change a calcualtion from z=x+y to z=x+y+a+b+c, it
> would need programmer intervention.
>
> Hence if I had the expression itself in a table and had a way of
> calculating it dynamically, it would help.

Thanks, that gives me at least a glimpse of the requirements. I'm still
not sure that this is an easy way to.

You could store an expression as @a + @b + @c, and then get that from
the table and put in an variable @expr. Then you could say:

SELECT @sql = '@res = ' + @expr
EXEC sp_executesql @expr, N'@a int, @b int, @c int, @res int OUTPUT',
@a, @b, @c, @result OUTPUT

If the formula would be changed to @a + 2 * @b / @c it would still
work. But if the formula would become @a + @b + @d / @e you would have
to change the way you compute the value.

You could parse the string to find out which the values are, but
that is a tedious and tricky business to do in T-SQL. A language like
Perl would be a lot nicer for this sort of work.

One idea that occurred to me is that you should not store the expressions
complete strings, but instead should have a table like:

CREATE TABLE expressiontokens (exprid int NOT NULL,
rowno smallint NOT NULL,
tokens varchar(30) NOT NULL,
CONSTRAINT pk_exprtmers PRIMARY KEY expressionterms(exprid, term))

Then for one expression you could have somehing like

rowno token
1 @a
2 +
3 @b
4 *
5 (
6 @c
7 +
...

@a, @b and @c would then be key values to a table where you would look up
the actual values. In this way would not have to parse the expression at
run-time. (But you would have to parse the expression to store it. If
you were do this in T-SQL, you would still have to build the SQL statement
dynamically.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Back to original message]