字符串示例： 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
1万+
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
