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,@val)) --得到单个节点的数量 A1=X

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

????????????? set @value=substring(@tmpVal,@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,@val)) --得到单个节点的数量 A1=X

set @user_oid=substring(@tmpVal,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 @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,len(@map))

if @nameStr is not null

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

if @valueStr is not null

set @valueStr=SubString(@valueStr,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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!