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

調用代碼：

-- =============================================
-- Author:        <over>
-- Create date: <2007/09/19>
-- 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)=','

可參考曾發過的一篇SQL SERVER2000 預存程序 設定傳入參數預設值文章。