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 存储过程 设置传入参数默认值文章。