[MSSQL]以字符','分隔的字段值，循环插入表中

 

declare @strChatRooms    nvarchar(1000)    --自定义参数，含有','符号的值
declare @UserID        int    --自定义参数
declare @tmpNum        int    --检查','字符，移动的位置
declare @tmpID        int    --获取分隔的值，用于插入表
declare @iSuccess    int    --返回查看错误
Begin Tran
--事例字符串
set @strChatRooms = '11,12,13,14,15,'
--初始化检查','符号位置，符号自定义
set @tmpNum=1
--进行循环将','符号分隔的数据插入信息表
while len(@strChatRooms) > 0
begin
    if substring(@strChatRooms,@tmpNum,1) = ','
    begin
       set @tmpID=cast(left(@strChatRooms,@tmpNum-1) as int)
       set @strChatRooms=right(@strChatRooms,len(@strChatRooms)-@tmpNum)
       set @tmpNum=1
       Insert into ChatAdmin(UserID,ChatRoomID)Values(@UserID,@tmpID)
        if @@error <> 0
             begin 
                  ROLLBACK TRAN
                  set  @iSuccess = -14
                  return
        end
    end
    else
    set  @tmpNum=@tmpNum+1
end
Commit Tran