--
=============================================
-- 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: <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
-- 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 存储过程 设置传入参数默认值文章。
本文转自博客园农民伯伯的博客,原文链接:SQLSERVER拆分字符串的函数(表值函数),如需转载请自行联系原博主。