i have some expression in string like (1+2(3*4) ) and i need to execute it in select statement

[ajaykumardev]

i have some expression in string like (1+2(3*4) ) and i need to execute it in select statement
to get corresponding expression result . can anyone suggest solution for this .

 

[djj55]

SELECT (1+2*(3*4) ) AS x

You will need to determin if the addition is done before or after the multiplication of two 2(3*4)

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[ajaykumardev]

SELECT '(1+(2*(3*4)) )' AS x that expression data type is string .

 

[feherke]

Hi

I suppose you have it in a variable like @expression :

[b]declare[/b] @expression [b]varchar[/b](100) = [green][i]'(1+(2*(3*4)) )'[/i][/green]
[b]exec[/b]([green][i]'select'[/i][/green] + @expression)

Will return 25.

Feherke.
feherke.github.io

 

[ajaykumardev]

right friend , but i need to get result in select statement.so, i cant use exec in select statement .

 

[SkipVought]

@ajaykumardev,

Did you carefully read and diligently evaluate ALL the previous suggestions???

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[feherke]

Hi

You mean, you need to have the result accessible in other expressions ?

[b]declare[/b] @expression [b]varchar[/b](100) = [green][i]'(1+(2*(3*4)) )'[/i][/green]
[b]declare[/b] @query [b]nvarchar[/b](100) = [green][i]'select @result = '[/i][/green] + @expression
[b]declare[/b] @param [b]nvarchar[/b](100) = [i]'@result int out'[/i]
[b]declare[/b] @result [b]int[/b]
[b]exec[/b] sp_executesql @query, @param, @result [b]out[/b]
[b]select[/b] [green][i]'the result'[/i][/green], @result, [green][i]'in your query'[/i][/green]

Or you mean the values 1, 2, 3 and 4 will be actually provided by the same [tt]select[/tt] in which you need the result ?


Feherke.
feherke.github.io

 

[ajaykumardev]

feherke frnd ..

I have one select statement like select empcode,rate,PerVal,dbo.replacetokens((empcode+(rate*(PerVal*4)) )) as Total from <Table Name>



In this select statement we are replacing the token in string by values . and then i need to get result same time when i execute the select statement , the prob is i cant use exec and sp_executesql in the select statement ...

 

[feherke]

Hi

I have one select statement like select empcode,rate,PerVal,dbo.replacetokens((empcode+(rate*(PerVal*4)) )) as Total from <Table Name>

Let us clarify abit more.

dbo.replacetokens() expects a numeric value, but you have the formula in a string. So you need something to evaluate it first, somehow like this :

[b]select[/b] empcode,rate,PerVal,dbo.replacetokens([highlight]dbo.evaluate([/highlight][green][i]'(empcode+(rate*(PerVal*4)) )'[/i][/green][highlight])[/highlight]) [b]as[/b] Total [b]from[/b] "Table Name"

Is this correct ? Is your string with the formula looking like that ?

Feherke.
feherke.github.io

 

[feherke]

Hi

Well, anyway. Is not possible. [tt]exec[/tt] and [tt]sp_executesql[/tt] are forbidden in functions.

Personally I would do it like this :

[gray]-- supposing we are working with this table...[/gray]
[b]create[/b] [b]table[/b] ajaykumardev (
  id [b]int[/b] [b]identity[/b],
  one [b]float[/b],
  two [b]float[/b],
  three [b]float[/b]
)

[gray]-- ... having these data[/gray]
[b]insert[/b] [b]into[/b] ajaykumardev (one, two, three) [b]values[/b] (1, 2, 3), (1.1, 2.2, 3.3)

[gray]-- 1) create a temporary copy of the table, including an additional field for the evaluation result[/gray]
[b]select[/b] *, [b]cast[/b]([b]null[/b] [b]as[/b] [b]float[/b]) replacetokens [b]into[/b] #temp [b]from[/b] ajaykumardev 

[gray]-- 2) declare the necessary variables[/gray]
[gray]-- similar as in my previous code on 2 Dec 13 9:35[/gray]
[b]declare[/b]
  @expression [b]varchar[/b](100) = [green][i]'(empcode+(rate*(perval*4)))'[/i][/green]
[b]declare[/b]
  @query [b]nvarchar[/b](100) = [green][i]'select @result = '[/i][/green] + replace(replace(replace(@expression, [green][i]'empcode'[/i][/green], [green][i]'@empcode'[/i][/green]), [green][i]'rate'[/i][/green], [green][i]'@rate'[/i][/green]), [green][i]'perval'[/i][/green][green][/green], [green][i]'@perval'[/i][/green]),
  @param [b]nvarchar[/b](100) = [green][i]'@empcode float, @rate float, @perval float, @result float out'[/i][/green],
  @result [b]float[/b],
[gray]-- variables for the fetched values[/gray]
  @id [b]int[/b],
  @one [b]float[/b],
  @two [b]float[/b],
  @three [b]float[/b]
[gray]-- and a cursor to fetch from[/gray]
[b]declare[/b]
  tempcursor [b]cursor[/b] [b]for[/b] [b]select[/b] id, one, two, three [b]from[/b] #temp

[gray]-- 3) loop over the records and perform the calculation for each[/gray]
[b]open[/b] tempcursor
[b]fetch[/b] [b]next[/b] [b]from[/b] tempcursor [b]into[/b] @id, @one, @two, @three
[b]while[/b] @@fetch_status = 0 [b]begin[/b]
[gray]-- evaluate the formula with the current values[/gray]
  [b]exec[/b] sp_executesql @query, @param, @one, @two, @three, @result [b]out[/b]
[gray]-- put the resulted value into the result field[/gray]
  [b]update[/b] #temp [b]set[/b] replacetokens = @result [b]where[/b] id = @id

  [b]fetch[/b] [b]next[/b] [b]from[/b] tempcursor [b]into[/b] @id, @one, @two, @three
[b]end[/b]
[b]close[/b] tempcursor
[b]deallocate[/b] tempcursor

[gray]-- 4) finally query the values you always wanted from the temporary table[/gray]
[b]select[/b] * [b]from[/b] #temp

id          one                    two                    three                  replacetokens
----------- ---------------------- ---------------------- ---------------------- ----------------------
1           1                      2                      3                      25
2           1,1                    2,2                    3,3                    30,14

Note that if you change your string with the formula to '(@empcode+(@rate*(@perval*4)))', then you will not need all those [tt]replace()[/tt] calls.
Also note that if the formula may be changed and more variables added, then the @param variable's value has to be adjusted. Preferably by writing a parser to do it.


Feherke.
feherke.github.io

 

[feherke]

Hi

As all the values used in the formula are in the same table, you can use a computed column too :

[b]create[/b] [b]table[/b] ajaykumardev (
  id [b]int[/b] [b]identity[/b],
  empcode [b]float[/b],
  rate [b]float[/b],
  perval [b]float[/b]
)

[b]insert[/b] [b]into[/b] ajaykumardev (empcode, rate, perval) [b]values[/b] (1, 2, 3), (1.1, 2.2, 3.3)

[b]select[/b] * [b]into[/b] #temp [b]from[/b] ajaykumardev

[b]declare[/b]
  @expression [b]varchar[/b](100) = [green][i]'(empcode+(rate*(perval*4)))'[/i][/green]

[b]exec[/b]([green][i]'alter table #temp add replacetokens as'[/i][/green] + @expression)

[b]select[/b] * [b]from[/b] #temp

id          empcode                rate                   perval                 replacetokens
----------- ---------------------- ---------------------- ---------------------- ----------------------
1           1                      2                      3                      25
2           1,1                    2,2                    3,3                    30,14

Feherke.
feherke.github.io

 

[ajaykumardev]

I think what i want here is not possible .Thanks for help friend.Now I m using cursor for this purpose .