Another SQL challenge ***SOLUTION FOUND - THANKS TO ALL***

[NuclearNed]

First off, I'm programming in Microsoft TSQL.

We have a table with a varchar field that contains mathematical formulas as strings. For example, the following would be a valid entry in this field:

"@var1 + @var2 / @var3" (NOTE THE QUOTES - THIS IS A STRING)

Some languages contain an "evaluate" function that allows something like the following:

SELECT @var1 = 1, @var2 = 2, @var3 = 3, @formula = "@var1 + @var2 / @var3"
SELECT evaluate(@formula)

The result of the code above would be that the formula stored in the string @formula would be evaluated using the variables that are named within the formula string.

To summarize, I need the ability to execute a formula that is 1) stored in a string, 2)not known until it is retrieved from a table, and 3) contains the names of variables that are already declared elsewhere in the function.

I suspect that there is an easy answer for this one, but I'll ask anyway: how do you do this in TSQL?

EDIT: Snapster has a nearly complete solution below, but now I've hit another snag. The line below doesn't work:

EXEC ('SELECT 5/2')

This line doesn't work because it always returns the truncation of the decimal answer (i.e. the example above returns 2). Does anyone have any suggestions?

 

[Snapster]

You would have to execute it as: Select @var1 + @var2 / @var3

If you want you can put all that in a string and do exec (@formula)

 

[NuclearNed]

Originally posted by: Snapster
You would have to execute it as: Select @var1 + @var2 / @var3

If you want you can put all that in a string and do exec (@formula)

Have a great weekend!!!

 

[NuclearNed]

Originally posted by: NuclearNed

Originally posted by: Snapster
You would have to execute it as: Select @var1 + @var2 / @var3

If you want you can put all that in a string and do exec (@formula)

Have a great weekend!!!

Damn - cookie revoked, but it nearly worked. Query Analyzer gives "Server: Msg 137, Level 15, State 2, Line 1 Must declare the variable '@var1'." for the code below:

DECLARE @formula varchar(500),
@var1 int,
@var2 int
SELECT @formula = 'SELECT @var1 + @var2 / 3',
@var1 = 1,
@var2 = 2
EXEC (@formula)


Also, I have to be able to select the result of the evaluation into a variable. Doing a "SELECT EXEC(string)" gives a syntax error.

 

[Snapster]

you can't put the vars directly into the string, you have to cast it to a string, then exec it. Sorry, shoulda been more clear on that!

Right, try this:


DECLARE @formula varchar(500),
@var1 int,
@var2 int,
@result decimal

SELECT @var1 = 1, @var2 = 2, @formula = 'SELECT ' + cast(@var1 as varchar) + ' + ' + cast(@var2 as varchar) + ' / 3'

-- Create a temp table to hold your value
create table #temp (result decimal)

-- Insert the value into temp table
insert into #temp
exec (@formula)

-- Grab the result
set @result = (select result from #temp)

-- Do something with it
print @result

-- Drop temp table
drop table #temp

Nasty, but unfortunately there is no other way to get the results of an exec

 

[NuclearNed]

Originally posted by: Snapster
you can't put the vars directly into the string, you have to cast it to a string, then exec it. Sorry, shoulda been more clear on that!

Right, try this:


DECLARE @formula varchar(500),
@var1 int,
@var2 int,
@result decimal

SELECT @var1 = 1, @var2 = 2, @formula = 'SELECT ' + cast(@var1 as varchar) + ' + ' + cast(@var2 as varchar) + ' / 3'

-- Create a temp table to hold your value
create table #temp (result decimal)

-- Insert the value into temp table
insert into #temp
exec (@formula)

-- Grab the result
set @result = (select result from #temp)

-- Do something with it
print @result

-- Drop temp table
drop table #temp

Nasty, but unfortunately there is no other way to get the results of an exec

Thanks for the answer - I posted my response above before I really thought about it much, and I eventually came up with the idea of constructing the string myself. However, I couldn't think of a way to select the result of the formula into a variable. Nice job, and thanks.

 

[NuclearNed]

AAAAARRRRRGGGGHHHHH!!!!!!

I'm still having trouble and to top it all my brain doesn't seem to be working.

The following code doesn't work:

EXEC ('SELECT 5 / 2').

The answer that is returned is 2 - the EXEC command is returning the truncated value instead of a decmal or real.

Any suggestions? Anyone?

 

[MrChad]

Originally posted by: NuclearNed
AAAAARRRRRGGGGHHHHH!!!!!!

I'm still having trouble and to top it all my brain doesn't seem to be working.

The following code doesn't work:

EXEC ('SELECT 5 / 2').

The answer that is returned is 2 - the EXEC command is returning the truncated value instead of a decmal or real.

Any suggestions? Anyone?

What about:

EXEC ('SELECT CONVERT(float, 5) / CONVERT(float, 2)')

 

[awal]

Originally posted by: NuclearNed
AAAAARRRRRGGGGHHHHH!!!!!!

I'm still having trouble and to top it all my brain doesn't seem to be working.

The following code doesn't work:

EXEC ('SELECT 5 / 2').

The answer that is returned is 2 - the EXEC command is returning the truncated value instead of a decmal or real.

Any suggestions? Anyone?

This is returning the INT value, because INT/INT = INT. IF you need the decimal places do 5.0/2.

EXEC ('SELECT 5.0 / 2')

 

[NuclearNed]

Originally posted by: MrChad

Originally posted by: NuclearNed
AAAAARRRRRGGGGHHHHH!!!!!!

I'm still having trouble and to top it all my brain doesn't seem to be working.

The following code doesn't work:

EXEC ('SELECT 5 / 2').

The answer that is returned is 2 - the EXEC command is returning the truncated value instead of a decmal or real.

Any suggestions? Anyone?

What about:

EXEC ('SELECT CONVERT(float, 5) / CONVERT(float, 2)')

I'll give it a try. My first attempt (below, from before I read your post) doesn't work:

EXEC ('SELECT CAST((5/2) AS decimal(20, 10))')


EDIT: Thanks to everyone involved - I now have a complete solution.

 

[NuclearNed]

Here is an example of how to do everthing described in the posts above:

 

[Slap]

nm