计算表达式列的值

最新推荐文章于 2025-08-07 14:59:42 发布

原创最新推荐文章于 2025-08-07 14:59:42 发布 · 588 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#insert #vbscript #function #table #sql #null

sp_configure 'Show Advanced Options' ,1
RECONFIGURE
go
sp_configure 'Ole Automation Procedures',1
RECONFIGURE
go

--计算表达式的值

create table Tb_UinonConfig(prim1 real,arithmetic varchar(50))
insert into Tb_UinonConfig select 100,'+20.5×15÷13.1'
insert into Tb_UinonConfig select 80 ,'÷2.5×100+23.0'
insert into Tb_UinonConfig select -80,'+100+(23.0×6)'
go

create function f_calc(
@str varchar(1000)--要计算的表达式
)returns sql_variant
as
begin
declare @re sql_variant

declare @err int,@src varchar(255),@desc varchar(255)
declare @obj int

exec @err=sp_oacreate 'MSScriptControl.ScriptControl',@obj out
if @err<>0 goto lb_err

exec @err=sp_oasetproperty @obj,'Language','vbscript'
if @err<>0 goto lb_err

exec @err=sp_oamethod @obj,'Eval',@re out,@str

exec sp_oadestroy @obj --关键，否则仅仅能计算256行
if @err=0 return(@re)

lb_err:
exec sp_oageterrorinfo NULL, @src out, @desc out
declare @errb varbinary(4),@s varchar(20)
set @errb=cast(@err as varbinary(4))
exec master..xp_varbintohexstr @errb,@s out
return('错误号: '+@s+char(13)+'错误源: '+@src+char(13)+'错误描述: '+@desc)
end
go

select prim1,arithmetic,
dbo.f_calc(cast(prim1 as varchar)+replace(replace(arithmetic,'×','*'),'÷','/')) as col from Tb_UinonConfig

drop function f_calc
drop table Tb_UinonConfig