[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