--> Title : MSSQL計算表達式的值常見解決方案
--> Author : wufeng4552
--> Date : 2009-11-25 08:15:08
if object_id('[tb]') is not null drop table [tb]
go
create table [tb] (ID int,val nvarchar(14))
insert into [tb]
select 1,'1*5' union all
select 2,'1+5' union all
select 3,'1.0/5' union all
select 4,'1/5' union all
select 5,'2*5+3' union all
select 6,'(8-5)*3'
--方法1 動態T-SQL
declare @sql varchar(8000)
set @sql=''
select @sql=@sql+'select ID='+ltrim(ID)+', val='+val+' union all ' from tb
set @sql=left(@sql,len(@sql)-10)
exec(@sql)
-->查詢結果
/*
ID val
----------- ---------------------------------------
1 5.000000
2 6.000000
3 0.200000
4 0.000000
5 13.000000
6 9.000000
(6 個資料列受到影響)
*/
--方法2 游標
declare @t table(ID int, val dec(18,2))
declare @s varchar(50),@id int
declare cur cursor for select ID,val from tb
open cur
fetch next from cur into @id,@s
while @@fetch_status=0
begin
insert @t exec('select '+@ID+','+@s)
fetch next from cur into @id,@s
end
close cur
deallocate cur
select * from @t
/*
ID val
----------- ---------------------------------------
1 5.00
2 6.00
3 0.20
4 0.00
5 13.00
6 9.00
(6 個資料列受到影響)
*/
--方法3 函數
if object_id('f_calc')is not null drop function f_calc
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
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(N'错誤号 '+@s+char(13)+N'错誤源: '+@src+char(13)+N'错误描述: '+@desc)
end
go
--以上方法要啟用OLE Automation Procedures 方法如下
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO
select ID,
dbo.f_calc(val)val
from tb
/*
ID val
----------- --------------
1 5
2 6
3 .2
4 .2
5 13
6 9
(6 個資料列受到影響)
*/