参考这个:计算表达式的值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)' gocreate function f_calc( @str varchar(1000)--要计算的表达式 )returns sql_variant as begin declare @re sql_variantdeclare @err int,@src varchar(255),@desc varchar(255) declare @obj intexec @err=sp_oacreate 'MSScriptControl.ScriptControl',@obj out if @err<>0 goto lb_errexec @err=sp_oasetproperty @obj,'Language','vbscript' if @err<>0 goto lb_errexec @err=sp_oamethod @obj,'Eval',@re out,@str 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 goselect 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 /*autoid col ----------- ----------- 1 1 2 1 3 2 4 2*/
select 和 函数中都不能执行存储过程,比较郁闷。
--trydeclare @tb table (col1 int,col2 int) insert into @tb select 1,2*3.5 union all select 2,4+4+5 select * from @tb
---把那一列改为 int alter table tb alter column col2 int
--> 测试数据: #tb if object_id('tb') is not null drop table tb go create table tb (col1 int,col2 varchar(5)) insert into tb select 1,'2*3.5' union all select 2,'4+4+5'if object_id('tc') is not null drop table tc go create table tc (col2 int) declare @s varchar(100)
declare cur cursor for select col2 from tb open cur fetch next from cur into @s while @@fetch_status=0 begin insert tc exec('select '+@s) fetch next from cur into @s end close cur deallocate cur go select * from tc col2 ----------- 7 13(2 行受影响)
--> 测试数据:[TB] if object_id('[TB]') is not null drop table [TB] create table [TB]([col1] int,[col2] varchar(5)) insert [TB] select 1,'2*3.5' union all select 2,'4+4+5'declare @s varchar(4000) set @s='' select @s=@s+' select col1='+left(col1,4) +',col2='+col2+' union all' from TB set @s=left(@s,len(@s)-10) exec(@s)/* col1 col2 ----------- ------------- 1 7.0 2 13.0*/drop table TB
参考这个:计算表达式的值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)'
gocreate function f_calc(
@str varchar(1000)--要计算的表达式
)returns sql_variant
as
begin
declare @re sql_variantdeclare @err int,@src varchar(255),@desc varchar(255)
declare @obj intexec @err=sp_oacreate 'MSScriptControl.ScriptControl',@obj out
if @err<>0 goto lb_errexec @err=sp_oasetproperty @obj,'Language','vbscript'
if @err<>0 goto lb_errexec @err=sp_oamethod @obj,'Eval',@re out,@str
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
goselect 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
/*autoid col
----------- -----------
1 1
2 1
3 2
4 2*/
--trydeclare @tb table (col1 int,col2 int)
insert into @tb select 1,2*3.5
union all select 2,4+4+5
select * from @tb
---把那一列改为 int
alter table tb alter column col2 int
if object_id('tb') is not null drop table tb
go
create table tb (col1 int,col2 varchar(5))
insert into tb
select 1,'2*3.5' union all
select 2,'4+4+5'if object_id('tc') is not null drop table tc
go
create table tc (col2 int)
declare @s varchar(100)
declare cur cursor for select col2 from tb
open cur
fetch next from cur into @s
while @@fetch_status=0
begin
insert tc exec('select '+@s)
fetch next from cur into @s
end
close cur
deallocate cur
go
select * from tc
col2
-----------
7
13(2 行受影响)
--> 测试数据:[TB]
if object_id('[TB]') is not null drop table [TB]
create table [TB]([col1] int,[col2] varchar(5))
insert [TB]
select 1,'2*3.5' union all
select 2,'4+4+5'declare @s varchar(4000)
set @s=''
select @s=@s+' select col1='+left(col1,4) +',col2='+col2+' union all' from TB
set @s=left(@s,len(@s)-10)
exec(@s)/*
col1 col2
----------- -------------
1 7.0
2 13.0*/drop table TB
两点问题:
1、邹老大这个函数需要打开OLE Automation选项,可能会对数据库安全造成影响?
2、该计算需要被Web调用,且数据量很大,所以需要避免使用游标处理。