MSSQL計算表達式的值常見解決方案

--> 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 個資料列受到影響)

*/