--创建存储过程

IF EXISTS (SELECT * FROM sysobjects WHERE name='GetSplitString' AND xtype='p')

    DROP PROCEDURE dbo.GetSplitString

GO

CREATE PROCEDURE dbo.GetSplitString

    @strID nvarchar(max)    --以逗号隔开的字符串

    ,@tableName nvarchar(50) output    --临时表

AS

BEGIN

    declare @totalLength int

    declare @length int

    declare @tag varchar(20)

    declare @currentNumber int

    set @tag=','    --分割符

    set @totalLength=len(@strID)

    --如果字符串不为空，则判断是否是以逗号结尾，避免漏掉最后一个字符值

    if @totalLength>0

    begin

        if right(@strID,1)<>','

            select @strID=@strID+','

    end

    set @length=charindex(@tag,@strID)

    exec('insert into '+@tableName+'(code)values(left('''+@strID+''','+@length+'-1))')

    set @strID=substring(@strID,@length+1,@totalLength)

    set @currentNumber=1

    while (@length>0)

    begin

        set @currentNumber=@currentNumber+1

        set @length=charindex(@tag,@strID)

        if(@length=0)

        begin

            break;

        end

        exec('insert into '+@tableName+'(code)values(left('''+@strID+''','+@length+'-1))')

        set @strID=substring(@strID,@length+1,@totalLength)

    end

END

GO

--调用存错过程

IF EXISTS (SELECT * FROM sysobjects WHERE name='tbTemp' AND xtype='U')

    DROP TABLE tbTemp

GO

create table tbTemp(code nvarchar(20))

go

exec GetSplitString 'baidu,google,yahoo,my,db,sqlserver,helloworld','tbTemp'

go

select * from tbTemp

go

IF EXISTS (SELECT * FROM sysobjects WHERE name='tbTemp' AND xtype='U')

    DROP TABLE tbTemp

GO