插入多行数据——函数及事务

 

 

 

 
 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=rtrim(ltrim(@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 InsertUserValue
 @valueids nvarchar(100),
 @userid nvarchar(50)
AS
BEGIN
 declare @next int
 set @next=1
 declare @count int
 declare @valID nvarchar(20)
 declare @TranErrer int
 set @TranErrer = 0
 begin transaction
  DELETE  FROM  t_UserValue WHERE  userID=@userid
         set @TranErrer = @TranErrer + @@error
 while @next<=dbo.Get_StrArrayLength(@valueids,',')
 begin
  set @valID = dbo.Get_StrArrayStrOfIndex(@valueids,',',@next)
  --print @valID
   
                    if(@valID !='' AND @valID IS NOT NULL )
   insert into t_UserValue values(@userid,@valID)
   set @TranErrer = @TranErrer + @@error
   
  set @next=@next+1
 end
 
 if(@TranErrer>0)
 begin
  rollback transaction
 end
 else
 begin
  commit transaction
 end
END
GO
--
--     exec InsertUserValue '291,290','56'
--   SELECT *  from dbo.t_UserValue WHERE userID=56