SQL将以字符’,’分隔的字段值,循环插入表中

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