SQLSERVER拆分字元串的函數(表值函數)

-- =============================================

-- Author:        <over>

-- Create date: <2007/09/19>

-- Description:    <string.split()拆分字元串>

ALTER FUNCTION [dbo].[Split]

    (

        @string varchar(255),        --1,2,3,45,

        @separator char=','

    )

RETURNS @temp TABLE 

(

    Item int

)

AS 

BEGIN

    DECLARE @Item int

    DECLARE @CurrentIndex  int

    DECLARE @NextIndex int

    DECLARE @Length  int    --字元串的長度

    SET @CurrentIndex=1

    SET @Length=DATALENGTH(@string)

    IF @string IS NOT NULL 

        BEGIN

            WHILE @CurrentIndex<@Length

                BEGIN

                    --CHARINDEX(子串,被搜尋的字元串)

                    SET @NextIndex=CHARINDEX(@separator,@string,@CurrentIndex)

                    SET @Item=SUBSTRING(@string,@CurrentIndex,@NextIndex-@CurrentIndex)

                    SET @CurrentIndex=@NextIndex+1

                    --把臨時變量的值放到要傳回的表中

                    INSERT INTO @temp VALUES(@Item)

                END

        END

    RETURN

END

調用代碼：

-- Description:    <更新使用者的角色(更改授權)>

ALTER PROCEDURE dbo.UpdUserRole

    @UserID INT,                --這裡沒有做UserID的檢查

    @RoleIDs VARCHAR(255)        --1,2,3,4,5

AS

    SET NOCOUNT ON 

    --單個權限值

    DECLARE @RoleID INT

    --獲得拆分之後的字元串

    DECLARE Roles CURSOR  FOR

    SELECT Item FROM [dbo].[Split](@RoleIDs,default)

    --for Read Only

    --删除原有權限

    DELETE FROM UserRoles WHERE UserID=@UserID

    OPEN Roles

    FETCH Roles INTO @RoleID

        WHILE(@@FETCH_STATUS=0)

            BEGIN

                --SELECT UserID FROM UserRoles WHERE UserID=@UserID AND RoleID=@RoleID

                --if @@rowcount=0

                INSERT INTO UserRoles VALUES(@UserID,@RoleID)

                --把下一個值塞給變量@RoleID

                FETCH NEXT FROM Roles

                INTO @RoleID

            END

    CLOSE Roles

    --DEALLOCATE 用于删除前面準備好的查詢。 

    --如果你沒有明确 DEALLOCATE 一個準備好的查詢， 那麼它在會話結束的時候删除。 

    DEALLOCATE Roles  

    --RETURN

注意：

此函數在SQLSERVER2005存儲過程中調用沒有問題，如果需要移植到SQLSERVER2000下，隻需把:

@separator char=','

換成

@separator char(1)=','