通过存储过程进行字符串分拆，在同时删除多条记录的时候有用。

1.存储过程

    ALTER PROCEDURE dbo.GetFirstWord

     @SourceString   NVARCHAR(4000) = NULL OUTPUT,

     @FirstWord      NVARCHAR(4000) = NULL OUTPUT

AS

    SET NOCOUNT ON

    DECLARE @Oldword        NVARCHAR(4000)

    DECLARE @Length         INT

    DECLARE @CommaLocation INT

 

    SELECT @Oldword = @SourceString

    IF NOT @Oldword IS NULL   

    BEGIN

        --get the first seperate chara location

        SELECT @CommaLocation = CHARINDEX(',',@Oldword)

        --get the length

        SELECT @Length = DATALENGTH(@Oldword)

        --Do Not Find Seperate,There is last Word                        

        IF @CommaLocation = 0                         

        BEGIN

             SELECT @FirstWord = @Oldword

             SELECT @SourceString = null

             RETURN @Length

            

        END

        -- get the first word

        SELECT @FirstWord = SUBSTRING(@Oldword,1,@CommaLocation-1)

        --trim the first word and separate

        SELECT @SourceString=SUBSTRING(@Oldword,@CommaLocation+1,@Length-@CommaLocation) 

           

        RETURN @Length - @CommaLocation

    END

    ELSE

        SELECT @FirstWord = null

        SELECT @Length = 0

    RETURN 0

------------------------------------------------

 

ALTER PROCEDURE dbo.DeleteUserdataByIdlist

     @idlist nvarchar(1000)

AS

     SET NOCOUNT ON

     DECLARE @FirstWORD NVARCHAR(1000)

     DECLARE @FIRSTID   int

     DECLARE @LENGTH    int

     SET @LENGTH = DATALENGTH(@idlist)

     while @LENGTH > 0

     BEGIN

         EXEC @LENGTH = GetFirstWord @idlist OUTPUT ,@FirstWORD OUTPUT

         if @LENGTH > 0

              BEGIN

              SELECT @FIRSTID = CONVERT(INT,@FirstWORD)

              EXEC DeleteUserdatabyID @userid = @FIRSTID

         END

     END

     RETURN 0

---------------------------------------------------------------------------------------------------------

2. 测试过程

在查询分析器中输入测试代码:

1.  declare @tmp1 nvarchar(4000),@tmp2 nvarchar(4000),@rtn int

   set @tmp1 = 'a,b,cde'

 

   SELECT @rtn = DATALENGTH(@tmp1)

   while @rtn > 0

   begin

     exec @rtn = GetFirstWord @SourceString = @tmp1 output, @firstWord = @tmp2 output

     print(@tmp2)

   end

2.  

exec DeleteUserdataByIdlist @idlist = '2,3,4,5,6'  --同时删除多条记录

3. 输出结果 

1.

        a

        b

        cde

2.

       删除了数据库中的记录