Create function Get_StrArrayLength
(
@str varchar(1024), --要分割的字符串
@split varchar(10) --分隔符号
)
returns int
as
begin
declare @location int
declare @start int
declare @length int
set @str=ltrim(rtrim(@str))
set @location=charindex(@split,@str)
set @length=1
while @location<>0
begin
set @start=@location+1
set @location=charindex(@split,@str,@start)
set @length=@length+1
end
return @length
end
go
Create function Get_StrArrayStrOfIndex
(
@str varchar(1024), --要分割的字符串
@split varchar(10), --分隔符号
@index int --取第几个元素
)
returns varchar(1024)
as
begin
declare @location int
declare @start int
declare @next int
declare @seed int
set @str=ltrim(rtrim(@str))
set @start=1
set @next=1
set @seed=len(@split)
set @location=charindex(@split,@str)
while @location<>0 and @index>@next
begin
set @start=@location+@seed
set @location=charindex(@split,@str,@start)
set @next=@next+1
end
if @location =0 select @location =len(@str)+1
return substring(@str,@start,@location-@start)
end
go
Create PROCEDURE InsertUserValuePro
@valueids nvarchar(100) ,
@userid nvarchar(50)
AS
BEGIN
declare @next int
set @next=1
declare @count int
declare @valID nvarchar(20)
while @next<=dbo.Get_StrArrayLength(@valueids,',')
begin
set @valID = dbo.Get_StrArrayStrOfIndex(@valueids,',',@next)
--print @valID
select @count=count(userID) from t_UserValue where userID=@userid and valueID = @valID
print @count
if(@valID ='' OR @valID IS NULL )
begin
DELETE FROM t_UserValue WHERE userID=@userid
end
ELSE if(@count <= 0)
begin
insert into t_UserValue values(@userid,@valID)
end
ELSE if(@count > 0)
begin
DELETE FROM t_UserValue WHERE userID=@userid
insert into t_UserValue values(@userid,@valID)
end
set @next=@next+1
end
END
GO
--exec InsertUserValuePro '','49'
--SELECT * from dbo.t_UserValue WHERE userID=49