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=11
select @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) 
)
go
declare @str varchar(max)
set @str=''
select @str=@str+' when FWebPrice='+QUOTENAME(FWebPrice,'''')+' then '+FWebPrice from #tb
set @str='
declare @SMM_Pb1  numeric(15,2) 
declare @SMM_Pb2  numeric(15,2) 
select @SMM_Pb1=11
select @SMM_Pb2=22 
insert #test 
select FName,case '+@str+' end,FA,FB from #tb'
print @str
exec(@str)

select * from #test
go
--创建临时表，以#test为元数据表拼接一下语句，解决FA字段：
declare @str2 varchar(max)
set @str2=''
declare @str3 varchar(max)
set @str3=''

select @str2=@str2+' when FA='+QUOTENAME(FA,'''')+' then '+FA from #test
set @str2=',case '+@str2+' end'
select @str3=@str3+' when FB='+QUOTENAME(FB,'''')+' then '+FB from #test
set @str3=',case '+@str3+' end'

set @str2='
declare @SMM_Pb1  numeric(15,2) 
declare @SMM_Pb2  numeric(15,2) 
select @SMM_Pb1=11
select @SMM_Pb2=22 
select FName,FWebPrice'
+@str2+@str3
+' from #test'
print @str2
exec(@str2)


declare @SMM_Pb1  numeric(15,2) 
declare @SMM_Pb2  numeric(15,2) 
select @SMM_Pb1=11
select @SMM_Pb2=22
select 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

------解决方案--------------------

SQL code

------------------听说参数是固定的，那就用了个偷懒的写法，试试吧，能不能满足你的情况。

--计算
create procedure sp_CalcExpression
@Expression varchar(8000)
,@ decimal(15,2) out
as
declare @sql nvarchar(4000)
set @sql = N'set @ = ' + @Expression
exec SP_EXECUTESQL @sql ,N'@ decimal(15,2) out', @ out
go


--先解决FWebPrice字段
create table #test
(
FName varchar(10) ,
FWebPrice varchar(100),
FWebPriceVal int,
FA varchar(max),
FB  varchar(max) 
)
go
declare @str varchar(max)
set @str=''
select @str=@str+' when FWebPrice='+QUOTENAME(FWebPrice,'''')+' then '+FWebPrice from #tb
set @str='
declare @SMM_Pb1  numeric(15,2) 
declare @SMM_Pb2  numeric(15,2) 
select @SMM_Pb1=11
select @SMM_Pb2=22 
insert #test 
select FName,FWebPrice,FWebPriceVal=case '+@str+' end, 
FA,FB from #tb'
exec(@str)
go
declare @SMM_Pb1  numeric(15,2) 
declare @SMM_Pb2  numeric(15,2) 
select @SMM_Pb1=11,@SMM_Pb2=22 
declare mycursor cursor
for
select 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 #test
open mycursor
select FName,FWebPrice,FA,FB into #tmp from #test where 1<>1
declare @FName varchar(10)