create table A(lie varchar(50)) insert into A values('1+4') insert into A values('5*5+1')create proc compute_all as begindeclare @tb table( a varchar(50), b int) declare @m float declare @v varchar(50) declare @sql Nvarchar(60) declare cr cursor for select lie from A open cr fetch next from cr into @v while @@fetch_status=0 begin set @sql='select @m=('+@v+')' exec sp_executesql @sql,N'@m float output',@m output insert into @tb values(@v,@m) --print cast(@m as varchar(50)) --print @v fetch next from cr into @v end close cr deallocate crselect * from @tbendexec compute_all(所影响的行数为 1 行)a b -------------------------------------------------- ----------- 1+4 5 5*5+1 26
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_calc]') and xtype in (N'FN', N'IF', N'TF')) drop function [dbo].[f_calc] GO/*--计算表达式的函数 计算给定算术表达式的值 此方法由VB版 hhjjhjhj(大头) 提供的VB处理程序程序上改造而来--邹建 2004.07(引用请注明此信息)--*//*--调用示例 select dbo.f_calc('123+456') --*/ 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('错误号: '+@s+char(13)+'错误源: '+@src+char(13)+'错误描述: '+@desc) end go
insert into A values('1+4')
insert into A values('5*5+1')create proc compute_all
as
begindeclare @tb table( a varchar(50), b int)
declare @m float
declare @v varchar(50)
declare @sql Nvarchar(60)
declare cr cursor
for select lie from A
open cr
fetch next from cr into @v
while @@fetch_status=0
begin set @sql='select @m=('+@v+')'
exec sp_executesql @sql,N'@m float output',@m output
insert into @tb values(@v,@m)
--print cast(@m as varchar(50))
--print @v
fetch next from cr into @v
end
close cr
deallocate crselect * from @tbendexec compute_all(所影响的行数为 1 行)a b
-------------------------------------------------- -----------
1+4 5
5*5+1 26
drop function [dbo].[f_calc]
GO/*--计算表达式的函数 计算给定算术表达式的值 此方法由VB版 hhjjhjhj(大头)
提供的VB处理程序程序上改造而来--邹建 2004.07(引用请注明此信息)--*//*--调用示例 select dbo.f_calc('123+456')
--*/
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('错误号: '+@s+char(13)+'错误源: '+@src+char(13)+'错误描述: '+@desc)
end
go