动态公式以及计算结果
create table #tb(FName varchar(10) , FWebPrice varchar(100),FA varchar(100),FB varchar(100) )insert into #tb values('地区A','@SMM_Pb1','FWebPrice*3','FA+3')insert into #tb values('地区B','@SMM_Pb2','(@SMM_Pb1+@SMM_Pb2)/2','@SMM_Pb1+FA') declare @SMM_Pb1 numeric(15,2) declare @SMM_Pb2 numeric(15,2) select @SMM_Pb1=11select @SMM_Pb2=22 select * from #tb怎么得出计算结果.公式是动态的,所以请问这个的动态语句怎么写？(要考虑到记录数可能很多,公式可能长点,拼接语句的话可能超过长度哦)create table #tb(FName varchar(10) ,FWebPrice varchar(100),FA varchar(100),FB varchar(100) )insert into #tb values('地区A','@SMM_Pb1','FWebPrice*3','FA+3')insert into #tb values('地区B','@SMM_Pb2','(@SMM_Pb1+@SMM_Pb2)/2','@SMM_Pb1+FA')--先解决FWebPrice字段create table #test(FName varchar(10) ,FWebPrice int,FA varchar(100),FB varchar(100) )godeclare @str varchar(max)set @str=''select @str=@str+' when FWebPrice='+QUOTENAME(FWebPrice,'''')+' then '+FWebPrice from #tbset @str='declare @SMM_Pb1 numeric(15,2) declare @SMM_Pb2 numeric(15,2) select @SMM_Pb1=11select @SMM_Pb2=22 insert #test select FName,case '+@str+' end,FA,FB from #tb'print @strexec(@str)select * from #testgo--创建临时表，以#test为元数据表拼接一下语句，解决FA字段：declare @str2 varchar(max)set @str2=''declare @str3 varchar(max)set @str3=''select @str2=@str2+' when FA='+QUOTENAME(FA,'''')+' then '+FA from #testset @str2=',case '+@str2+' end'select @str3=@str3+' when FB='+QUOTENAME(FB,'''')+' then '+FB from #testset @str3=',case '+@str3+' end'set @str2='declare @SMM_Pb1 numeric(15,2) declare @SMM_Pb2 numeric(15,2) select @SMM_Pb1=11select @SMM_Pb2=22 select FName,FWebPrice'+@str2+@str3+' from #test'print @str2exec(@str2)declare @SMM_Pb1 numeric(15,2) declare @SMM_Pb2 numeric(15,2) select @SMM_Pb1=11select @SMM_Pb2=22select FName,FWebPrice,case when FA='FWebPrice*3' then FWebPrice*3 when FA='(@SMM_Pb1+@SMM_Pb2)/2' then (@SMM_Pb1+@SMM_Pb2)/2 end,case when FB='FA+3' then FA+3 when FB='@SMM_Pb1+FA' then @SMM_Pb1+FA end from #test
[解决办法]
------------------听说参数是固定的，那就用了个偷懒的写法，试试吧，能不能满足你的情况。--计算create procedure sp_CalcExpression@Expression varchar(8000),@ decimal(15,2) outasdeclare @sql nvarchar(4000)set @sql = N'set @ = ' + @Expressionexec SP_EXECUTESQL @sql ,N'@ decimal(15,2) out', @ outgo--先解决FWebPrice字段create table #test(FName varchar(10) ,FWebPrice varchar(100),FWebPriceVal int,FA varchar(max),FB varchar(max) )godeclare @str varchar(max)set @str=''select @str=@str+' when FWebPrice='+QUOTENAME(FWebPrice,'''')+' then '+FWebPrice from #tbset @str='declare @SMM_Pb1 numeric(15,2) declare @SMM_Pb2 numeric(15,2) select @SMM_Pb1=11select @SMM_Pb2=22 insert #test select FName,FWebPrice,FWebPriceVal=case '+@str+' end, FA,FB from #tb'exec(@str)godeclare @SMM_Pb1 numeric(15,2) declare @SMM_Pb2 numeric(15,2) select @SMM_Pb1=11,@SMM_Pb2=22 declare mycursor cursorforselect FName,FWebPriceVal, FA=replace(replace(replace(FA,'@SMM_Pb1',@SMM_Pb1),'@SMM_Pb2',@SMM_Pb2),'FWebPrice','('+cast(FWebPriceVal as varchar(10))+')'), FB=replace(replace(replace(replace(FB,'@SMM_Pb1',@SMM_Pb1),'@SMM_Pb2',@SMM_Pb2),'FWebPrice','('+cast(FWebPriceVal as varchar(10))+')'), 'FA','('+replace(replace(replace(FA,'@SMM_Pb1',@SMM_Pb1),'@SMM_Pb2',@SMM_Pb2),'FWebPrice','('+cast(FWebPriceVal as varchar(10))+')')+')')from #testopen mycursorselect FName,FWebPrice,FA,FB into #tmp from #test where 1<>1declare @FName varchar(10),@FWebPriceVal int,@FA varchar(max),@FB varchar(max)fetch next from mycursor into @FName,@FWebPriceVal,@FA,@FBwhile @@fetch_status=0begin declare @FAVal numeric(15,2),@FBVal numeric(15,2) declare @x decimal(15,2) exec sp_CalcExpression @FA, @x out set @FAVal=@x exec sp_CalcExpression @FB, @x out set @FBVal=@x insert #tmp select @FName,@FWebPriceVal,@FAVal,@FBVal fetch next from mycursor into @FName,@FWebPriceVal,@FA,@FBendclose mycursordeallocate mycursorselect * from #tmp====================================================================FName FWebPrice FA FB ---------- ---------------- -------------------- ----------地区A 11 33.00 36.00 地区B 22 16.50 27.50
[解决办法]
--> Title : MSSQL計算表達式的值常見解決方案--> Author : wufeng4552--> Date : 2009-11-25 08:15:08if object_id('[tb]') is not null drop table [tb]gocreate table [tb] (ID int,val nvarchar(14))insert into [tb]select 1,'1*5' union allselect 2,'1+5' union allselect 3,'1.0/5' union allselect 4,'1/5' union allselect 5,'2*5+3' union allselect 6,'(8-5)*3'--方法1 動態T-SQLdeclare @sql varchar(8000)set @sql=''select @sql=@sql+'select ID='+ltrim(ID)+', val='+val+' union all ' from tbset @sql=left(@sql,len(@sql)-10)exec(@sql)-->查詢結果/*ID val----------- ---------------------------------------1 5.0000002 6.0000003 0.2000004 0.0000005 13.0000006 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 tbopen cur fetch next from cur into @id,@swhile @@fetch_status=0begin insert @t exec('select '+@ID+','+@s) fetch next from cur into @id,@sendclose curdeallocate cur select * from @t/*ID val----------- ---------------------------------------1 5.002 6.003 0.204 0.005 13.006 9.00(6 個資料列受到影響)*/--方法3 函數if object_id('f_calc')is not null drop function f_calcgocreate function f_calc(@str varchar(1000)--要计 oa的表达 |?)returns sql_variantasbegindeclare @re sql_variantdeclare @err int,@src varchar(255),@desc varchar(255)declare @obj intexec @err=sp_oacreate 'MSScriptControl.ScriptControl',@obj outif @err<>0 goto lb_errexec @err=sp_oasetproperty @obj,'Language','vbscript'if @err<>0 goto lb_errexec @err=sp_oamethod @obj,'Eval',@re out,@strif @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 outreturn(N'错誤号 '+@s+char(13)+N'错誤源: '+@src+char(13)+N'错误描述: '+@desc)endgo--以上方法要啟用OLE Automation Procedures 方法如下sp_configure 'show advanced options', 1;GORECONFIGURE;GOsp_configure 'Ole Automation Procedures', 1;GORECONFIGURE;GOselect ID, dbo.f_calc(val)valfrom tb/*ID val----------- --------------1 52 63 .24 .25 136 9(6 個資料列受到影響)*/本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/wufeng4552/archive/2009/11/25/4868138.aspx