yurrea
asked on
Evaluating a string into a decimal type
This is my problem:
Server: Msg 8164, Level 16, State 1, Procedure proc_Evaluate_Formula, Line 73
An INSERT EXEC statement cannot be nested.
I have a procedure that calls another stored procedure. In the first procedure, I have an INSERT EXEC statement. In the seconde procedure, I also have an INSERT EXEC statement. Now, this is not allowed. Is there any other way to get around this?
More Info:
In the second procedure, I use INSERT EXEC to evaluate a string that looks like this 'SELECT 100 + 200 + 300' to a decimal type in the table where I INSERT EXEC. This is done to evaluate '100 + 200 + 300'. Is there any other way to evaluate a string that looks similar to a formula into it's decimal value?
Server: Msg 8164, Level 16, State 1, Procedure proc_Evaluate_Formula, Line 73
An INSERT EXEC statement cannot be nested.
I have a procedure that calls another stored procedure. In the first procedure, I have an INSERT EXEC statement. In the seconde procedure, I also have an INSERT EXEC statement. Now, this is not allowed. Is there any other way to get around this?
More Info:
In the second procedure, I use INSERT EXEC to evaluate a string that looks like this 'SELECT 100 + 200 + 300' to a decimal type in the table where I INSERT EXEC. This is done to evaluate '100 + 200 + 300'. Is there any other way to evaluate a string that looks similar to a formula into it's decimal value?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hey don't worry, I'll accept your answer anyway. :)
hi yurrea,
thanks for the points. sorry, i was occupied by development so couldn't check here before.
i don't know what you do wrong when hard-coding (unless you enclose the formula between quotes), because the code below works as well.
set nocount on
declare @i decimal
create table #t1(result decimal)
--exec (@sql)
exec ('insert into #t1 select 100+200+300 as result')
select @i = (select top 1 result from #t1)
drop table #t1
print @i
this would not work:
exec ('insert into #t1 select '100+200+300' as result')
nor this
exec ('insert into #t1 select cast('100+200+300' as decimal) as result')
but anyway, i guess you need a general formula evaluator, that takes the formula as a parameter, right?
cheers,
george
thanks for the points. sorry, i was occupied by development so couldn't check here before.
i don't know what you do wrong when hard-coding (unless you enclose the formula between quotes), because the code below works as well.
set nocount on
declare @i decimal
create table #t1(result decimal)
--exec (@sql)
exec ('insert into #t1 select 100+200+300 as result')
select @i = (select top 1 result from #t1)
drop table #t1
print @i
this would not work:
exec ('insert into #t1 select '100+200+300' as result')
nor this
exec ('insert into #t1 select cast('100+200+300' as decimal) as result')
but anyway, i guess you need a general formula evaluator, that takes the formula as a parameter, right?
cheers,
george
ASKER
you're right george!
thanks again.
Yvann
thanks again.
Yvann
ASKER
I'm a little curious about the code that you gave. Why is it that when you do it your way (executing a dynamic query), it works. But when I try to play around with it - I hard-coded the dynamic query, it doesn't work? Why is that? The '100 + 200 + 300' is not evaluated when it's hard-coded.