34,575
社区成员
发帖
与我相关
我的任务
分享
/*以下可以直接用*/
declare @centers nvarchar(2000)
declare @sql varchar(2000)
set @centers='17462.72/22.88*35.05+(0.0+17265.60)+29149.12';
select @centers --结果:17462.72/22.88*35.05+(0.0+17265.60)+29149.12
set @sql='SELECT ROUND('+@centers+', 2)'
exec(@sql)--结果:73165.960000000
--我想把它放到函数里,返回73165.96
--但是函数里不能用exec
/*我转成以下sql代码*/
declare @centers nvarchar(2000)
declare @sql varchar(2000)
set @centers='17462.72/22.88*35.05+(0.0+17265.60)+29149.12';
select @centers --结果:17462.72/22.88*35.05+(0.0+17265.60)+29149.12
set @sql=ROUND(@centers, 2); --错误:Error converting data type nvarchar to float.
select @sql
--拼串执行可以,直接执行就不行
--寻找个解决方案,谢谢大家
--注:我要把它放到函数里,函数里有很多东西不能用。
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
--调用
declare @centers nvarchar(2000)
declare @sql varchar(2000)
set @centers='17462.72/22.88*35.05+(0.0+17265.60)+29149.12'
select
dbo.f_calc(@centers) as col
drop function f_calc
/*
col
--------------------
73165.9584615385
(所影响的行数为 1 行)
*/
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
--调用
declare @centers nvarchar(2000)
declare @sql varchar(2000)
set @centers='17462.72/22.88*35.05+(0.0+17265.60)+29149.12'
select
dbo.f_calc(replace(replace(@centers,'*','*'),'/','/')) as col
drop function f_calc
/*
col
--------------------
73165.9584615385
(所影响的行数为 1 行)
*/
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Xml;
using System.Xml.XPath;
public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString myEVAL(string str)
{
XmlDocument xd = new XmlDocument();
XPathNavigator nav = xd.CreateNavigator();
return nav.Evaluate(str).ToString();
}
};
SELECT dbo.myEVAL('1+2+3')
/*6*/
--在函数最开始增加一行:
set @bds = replace(@bds,' ','')--去空格,免得麻烦。
--然后修改下面:
if charindex(@c2,'.0123456789') > 0 or (@c2 = '-' and charindex(@c1,'.0123456789')=0)
--==》》
if charindex(@c2,'.0123456789') > 0 or (@c2 = '-' and @c1 in('','*','-','+','/','('))