EXECUTE AS question: Executing Simple Statements

Hi Gurus,

This is a question that I have for T-sql programmers.

Problem: To execute a statement with aritmetic operators in it.

Database: SQL Server 2000

For e.g. if my string has "5+6+9" I need to execute the string and
obtain a value of 20 in a variable. I read a little bit of
documentation on preparing a statement etc, but before going deeper
there, thought will write you you guys.

Thanks in advance!

Bhaskarsp_executesql is probably the best option:

declare
@.s varchar(100),
@.sql nvarchar(4000),
@.i int

set @.s = '5+6+9'
set @.sql = 'set @.i = ' + @.s
exec sp_executesql @.sql, N'@.i int output', @.i = @.i output
select @.i

But in general dynamic SQL can be awkward and there are a number of
important issues to consider - see here for the full story:

http://www.sommarskog.se/dynamic_sql.html

Alternatively, you could do this easily in a front-end application, and
if you have more complex formulae to evaluate then that might be a
better option.

Simon|||Thanks much Simon,

It worked fine for me. However, is there a way I can use variables
within the statement?

set @.a = 5
set @.b = 9
set @.c = 11

i.e. @.str = '@.a + @.b + @.c"

and then be able to execute str?

I am trying to make calculations dynamic.

Thanks much!!!|||Thanks much Simon,

It worked fine for me. However, is there a way I can use variables
within the statement?

set @.a = 5
set @.b = 9
set @.c = 11

i.e. @.str = '@.a + @.b + @.c"

and then be able to execute str?

I am trying to make calculations dynamic.

Thanks much!!!|||Bkr (keepitliteus@.yahoo.com) writes:
> It worked fine for me. However, is there a way I can use variables
> within the statement?
> set @.a = 5
> set @.b = 9
> set @.c = 11
> i.e. @.str = '@.a + @.b + @.c"
> and then be able to execute str?
> I am trying to make calculations dynamic.

Maybe, but probably not. Or put in another way: this is not something
you normally do in SQL Server. You may still have a very good reason
for wanting to do this, but there is also the possibility that you
are approaching your real business problem incorrectly.

So I suggest that you give an overview of the real-world probelm
where these expressions come in.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Bkr (keepitliteus@.yahoo.com) writes:
> It worked fine for me. However, is there a way I can use variables
> within the statement?
> set @.a = 5
> set @.b = 9
> set @.c = 11
> i.e. @.str = '@.a + @.b + @.c"
> and then be able to execute str?
> I am trying to make calculations dynamic.

So I suggest that you give an overview of the real-world probelm
where these expressions come in.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||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.

I will definately explore other methods of doing the same, but my
thought is there is a strong reason to have a functionality like this.

Thanks again
Bkr|||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.