sqlserver函数+过程，解析字符串信息作为字段，并动态更新数据表

 字符串示例： user_oid=$124;A1=1;A2=2;branch_no=1400; 

/*

创建字段处理函数

*/

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS OFF

GO

CREATE FUNCTION F_NODE_21(@val varchar(4000))

RETURNS @ctb TABLE([name] varchar(50),[value] varchar(50))

AS

BEGIN

       declare  @tmpVal  varchar(2000)

       declare  @name  varchar(50)

       declare  @value  varchar(50)

 

       while  charindex(';',@val)>0

      begin  

                 set @val = substring(@val,CHARINDEX(';', @val)+1,2000)--去掉已经计算过的节点

              set @tmpVal = substring(@val,0,CHARINDEX(';', @val)) --得到单个节点的数量 A1=X

              set @name=substring(@tmpVal,0,CHARINDEX('=', @tmpVal))

              set @value=substring(@tmpVal,CHARINDEX('=', @tmpVal)+1,10)

              if len(@name)>0   and  len(@value)>0

                begin

                     insert   @ctb

                      SELECT   @name as name,@value as value

               end

      end  

       RETURN

END

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

/*

创建插入节点表的存储过程

*/

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_NULLS ON

GO

 

CREATE procedure sp_insert_node_21

@content   varchar(4000)

as

declare  @num  int

declare  @val  varchar(4000)

declare  @tmpVal  varchar(4000)

declare  @user_oid varchar(200)

declare  @sqlStr  varchar(4000)

      

if @content <> ''

set @val=@content

set @tmpVal = substring(@val,0,CHARINDEX(';', @val)) --得到单个节点的数量 A1=X

set @user_oid=substring(@tmpVal,CHARINDEX('=', @tmpVal)+1,len(@tmpVal))

 

declare  @map  varchar(2000)

declare  @nameStr  varchar(2000)

declare  @valueStr  varchar(2000)

declare  @name varchar(200)

declare  @value varchar(200)

set @map='';

set @nameStr='';

set @valueStr='';

DECLARE   CustomCursor   Cursor   For   (Select   name,value   From   cc_dbo.F_NODE_21(@content))

OPEN         CustomCursor

       FETCH   NEXT   FROM   CustomCursor   INTO   @name,@value  ;

        WHILE   @@FETCH_STATUS   =   0

        BEGIN  

              if(@name='user_oid' or @name='branch_no' or @name='operate_type')

                     begin

                            set @map=@map+@name+'='''+@value+''',';

                            set @nameStr=@nameStr+@name+',';

                            set @valueStr=@valueStr+''''+@value+''',';

                     end

              else

                       begin

                            set @map=@map+@name+'='+@value+',';

                            set @nameStr=@nameStr+@name+',';

                            set @valueStr=@valueStr+@value+',';

                     end

                FETCH   NEXT   FROM   CustomCursor   INTO   @name,@value   ;

        END;

CLOSE   CustomCursor

DEALLOCATE   CustomCursor

 

if @map is not null

set @map=SubString(@map,0,len(@map))

if @nameStr is not null

set @nameStr=SubString(@nameStr,0,len(@nameStr))

if @valueStr is not null

set @valueStr=SubString(@valueStr,0,len(@valueStr))

      

begin

 select @num = (select count(1) from node_info_test where user_oid=@user_oid )

 set @sqlStr='';

 if @num > 0

  begin

      set @sqlStr='update node_info_test set '+ @map +' where user_oid='''+@user_oid+'''';

   end

 else --新插入一条

  begin

      set @sqlStr='insert into node_info_test(count_date,user_oid,'+@nameStr+') values (CONVERT(varchar,  getDate(), 20),'''+@user_oid+''','+@valueStr+')'

  end

end

 

execute(@sqlStr)

 

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO