SQL根据数组标识添加多条记录的例子

SQL根据数组标识添加多条记录的例子

DECLARE   @UserID   varchar ( 20 )
DECLARE   @RoleID   varchar ( 2000 )
SET   @UserID = ' 1 '
SET   @RoleID = ' 445,33,43554,55,556, '
DELETE   FROM  userole  WHERE  userid = @UserID
SET   @RoleID = stuff ( @RoleID , len ( @RoleID ), 1 , '' )

set   @RoleID = @RoleID + ' , '
DECLARE   @strSQL   nvarchar ( 2000 )
while   charindex ( ' , ' , @RoleID ) > 0
begin

select   @strSQL = left ( @RoleID , charindex ( ' , ' , @RoleID ) - 1 )

print ( ' INSERT INTO userole(userid,roleid)VALUES( ''' + @UserID + ''' , ''' + @strSQL + ''' ) ' )
set   @RoleID = stuff ( @RoleID , 1 , charindex ( ' , ' , @RoleID ), '' )

end