SQL动态计算出表中公式示例

需求贴：http://topic.csdn.net/u/20100513/16/8f29d352-d946-47e8-9d0c-0bc1c7e8db0a.html?013311098457492643

 

----------------------------------------------------------------------------------
-- Author : htl258(Tony)
-- Date   : 2010-05-13 20:51:15
-- Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
--          Jul  9 2008 14:43:34
--          Copyright (c) 1988-2008 Microsoft Corporation
--          Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)
-- Blog   : http://blog.csdn.net/htl258

-- Subject:SQL动态计算出表中公式示例
----------------------------------------------------------------------------------

--> 生成测试数据表: [tb]
IF OBJECT_ID('[tb]') IS NOT NULL
    DROP TABLE [tb]
GO
CREATE TABLE [tb] ([fitemid] [int],[FID_V] [nvarchar](10),[FName] [nvarchar](10),[FValue] [numeric](14,10),[FExpression] [nvarchar](30))
INSERT INTO [tb]
SELECT '8903','HBase','红本价','740.0000000000','HBase' UNION ALL
SELECT '8905','HBase','红本价','917.0000000000','HBase' UNION ALL
SELECT '8929','T1003','1KV','.0000000000','HBase+T1003+T1005' UNION ALL
SELECT '8929','T1005','1000欧姆','.0000000000','HBase+T1003+T1005' UNION ALL
SELECT '8929','HBase','红本价','200.0000000000','HBase+T1003+T1005' UNION ALL
SELECT '8931','HBase','红本价','288.0000000000','HBase+T1003+T1005' UNION ALL
SELECT '8931','T1005','1000欧姆','.0000000000','HBase+T1003+T1005' UNION ALL
SELECT '8931','T1003','1KV','.0000000000','HBase+T1003+T1005' UNION ALL
SELECT '8933','HBase','红本价','450.0000000000','HBase' UNION ALL
SELECT '8935','HBase','红本价','600.0000000000','HBase' UNION ALL
SELECT '9015','HBase','红本价','740.0000000000','(HBase+T1003+T1006)*T1009' UNION ALL
SELECT '9015','T1003','1KV','.0000000000','(HBase+T1003+T1006)*T1009' UNION ALL
SELECT '9015','T1006','100欧姆','.0000000000','(HBase+T1003+T1006)*T1009' UNION ALL
SELECT '9015','T1009','红绿色','11.0000000000','(HBase+T1003+T1006)*T1009' UNION ALL
SELECT '9017','T1009','红绿色','11.0000000000','HBase' UNION ALL
SELECT '9017','T1005','1000欧姆','.0000000000','HBase' UNION ALL
SELECT '9017','T1003','1KV','.0000000000','HBase' UNION ALL
SELECT '9017','HBase','红本价','965.0000000000','HBase' UNION ALL
SELECT '9019','HBase','红本价','1270.0000000000','HBase' UNION ALL
SELECT '9021','HBase','红本价','1790.0000000000','HBase' UNION ALL
SELECT '9029','HBase','红本价','200.0000000000','HBase' UNION ALL
SELECT '9031','HBase','红本价','288.0000000000','HBase'

SELECT * FROM [tb]

-->SQL查询如下:
--1.开启Ole Automation Procedures对象支持

sp_configure 'show advanced options',1
reconfigure
go
sp_configure 'Ole Automation Procedures',1
reconfigure
go

if object_id('f_calctotable') is not null
    drop function [dbo].[f_calctotable]
GO
/*--计算给定算术表达式(公式)的值
    计算给定算术表达式的值
    此方法由VB版hhjjhjhj(大头)
    提供的VB处理程序程序上改造而来
    --邹建2004.07(引用请注明此信息)--*/
    /*--调用示例
    select dbo.f_calc('123+456')
--*/


create function f_calctotable(
@fitemid int,
@str nvarchar(1000)   --要计算的表达式
)returns decimal(18,2)
as
begin
    select @str=replace(@str,[FID_V],[FValue]) from tb where fitemid=@fitemid
    declare @re decimal(18,2)
    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
select *,dbo.f_calctotable(fitemid,FExpression) fsumvalue from tb
/*
fitemid     FID_V      FName      FValue                                  FExpression                    fsumvalue
----------- ---------- ---------- --------------------------------------- ------------------------------ ---------------------------------------
8903        HBase      红本价        740.0000000000                          HBase                          740.00
8905        HBase      红本价        917.0000000000                          HBase                          917.00
8929        T1003      1KV        0.0000000000                            HBase+T1003+T1005              200.00
8929        T1005      1000欧姆     0.0000000000                            HBase+T1003+T1005              200.00
8929        HBase      红本价        200.0000000000                          HBase+T1003+T1005              200.00
8931        HBase      红本价        288.0000000000                          HBase+T1003+T1005              288.00
8931        T1005      1000欧姆     0.0000000000                            HBase+T1003+T1005              288.00
8931        T1003      1KV        0.0000000000                            HBase+T1003+T1005              288.00
8933        HBase      红本价        450.0000000000                          HBase                          450.00
8935        HBase      红本价        600.0000000000                          HBase                          600.00
9015        HBase      红本价        740.0000000000                          (HBase+T1003+T1006)*T1009      8140.00
9015        T1003      1KV        0.0000000000                            (HBase+T1003+T1006)*T1009      8140.00
9015        T1006      100欧姆      0.0000000000                            (HBase+T1003+T1006)*T1009      8140.00
9015        T1009      红绿色        11.0000000000                           (HBase+T1003+T1006)*T1009      8140.00
9017        T1009      红绿色        11.0000000000                           HBase                          965.00
9017        T1005      1000欧姆     0.0000000000                            HBase                          965.00
9017        T1003      1KV        0.0000000000                            HBase                          965.00
9017        HBase      红本价        965.0000000000                          HBase                          965.00
9019        HBase      红本价        1270.0000000000                         HBase                          1270.00
9021        HBase      红本价        1790.0000000000                         HBase                          1790.00
9029        HBase      红本价        200.0000000000                          HBase                          200.00
9031        HBase      红本价        288.0000000000                          HBase                          288.00

(22 行受影响)
*/

 

 

--邹老大写的原函数：

/*--计算给定算术表达式(公式)的值
    计算给定算术表达式的值
    此方法由VB版hhjjhjhj(大头)
    提供的VB处理程序程序上改造而来
    --邹建2004.07(引用请注明此信息)--*/
    /*--调用示例
    select dbo.f_calc('123+456')
--*/

if object_id('f_calc') is not null
    drop function [dbo].[f_calc]
GO
create function f_calc(
@str nvarchar(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