拆分公式函数及应用

/*
功能： 拆分公式函数
示例:   select * from F_Hr_SplitFormula('a=[X] and b=[Y]') order by ID
历史：
 2012-06-28 Chelen 创建
*/
create function F_Hr_SplitFormula
 (
 @Formula nvarchar(4000)
 )
returns
 @T table
  (
  ID int identity(1,1),
  Param nvarchar(4000)
  )
as
begin
 declare @Posit int = 0
 while CHARINDEX('[', @Formula) > 0 and CHARINDEX(']', @Formula) > 0
 begin
  set @Posit = CHARINDEX('[', @Formula)         
  insert into @T(Param) values (Substring(@Formula, 1, @Posit -1)) -- 将"["前的字符串插入到临明表
  set @Formula = SUBSTRING(@Formula, @Posit, LEN(@Formula))   -- 截断"["前的字符串
  --
  select @Posit = CHARINDEX(']', @Formula)         
  if @Posit <> 0
  begin
   insert into @T(Param) values (Substring(@Formula, 1, @Posit)) -- 将"["和"]"间字符串(即参数)插入到临明表     
   set @Formula = SUBSTRING(@Formula, @Posit + 1, LEN(@Formula)) -- 截断"]"前的字符串    
  end
 end
 
 if @Formula <> ''
 begin
  insert into @T(Param) values (@Formula) --将剩余的字符串插入到临明表     
 end
   
 return
end

【示例】
if OBJECT_ID('Tempdb..#Param') is not null
 drop table #Param  

create table #Param(ID int identity(1,1), ParamName nvarchar(50), ParamValue nvarchar(50))
insert into #Param(ParamName, ParamValue) values('语文', '80' )
insert into #Param(ParamName, ParamValue) values('数学', '95' )
insert into #Param(ParamName, ParamValue) values('英语', '90' )
  
declare @Formula nvarchar(4000) = '(case when [语文]+[数学]+[英语] > 270 then ''优'' when [语文]+[数学]+[英语]<180 then ''不合格'' else ''合格'' end)'

--拆分后的数据集
select * from F_Hr_SplitFormula(@Formula) f order by f.ID

select @Formula = (case when f.ID=1 then '' else @Formula end) + (case when p.ParamValue is not null then p.ParamValue else f.Param end)
from F_Hr_SplitFormula(@Formula) f
left join #Param p on '[' + p.ParamName + ']' = f.Param
order by f.ID

-- 替换后结果
select @Formula

-- 执行结果
Execute('Select ' + @Formula)
  
drop table #Param