插入多行数据——存储过程加函数

 

 

 
 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