[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