创建表值函数Split
Create FUNCTION [dbo].[split]
(
@Long_str VARCHAR(8000) ,
@split_str VARCHAR(100)
)
RETURNS @tmp TABLE
(
ID INT IDENTITY
PRIMARY KEY ,
short_str VARCHAR(8000)
)
AS
BEGIN
DECLARE @long_str_Tmp VARCHAR(8000) ,
@short_str VARCHAR(8000) ,
@split_str_length INT
SET @split_str_length = LEN(@split_str)
IF CHARINDEX(@split_str, @Long_str) <= 0
BEGIN
INSERT INTO @tmp
SELECT @Long_str
END
ELSE
BEGIN
IF CHARINDEX(@split_str, @Long_str) = 1
SET @long_str_Tmp = SUBSTRING(@Long_str,
@split_str_length + 1,
LEN(@Long_str)
- @split_str_length)
ELSE
SET @long_str_Tmp = @Long_str
IF CHARINDEX(REVERSE(@split_str), REVERSE(@long_str_Tmp)) > 1
SET @long_str_Tmp = @long_str_Tmp + @split_str
ELSE
SET @long_str_Tmp = @long_str_Tmp
WHILE CHARINDEX(@split_str, @long_str_Tmp) > 0
BEGIN
SET @short_str = SUBSTRING(@long_str_Tmp, 1,
CHARINDEX(@split_str,
@long_str_Tmp)
- 1)
DECLARE @long_str_Tmp_LEN INT ,
@split_str_Position_END INT
SET @long_str_Tmp_LEN = LEN(@long_str_Tmp)
SET @split_str_Position_END = LEN(@short_str)
+ @split_str_length
SET @long_str_Tmp = REVERSE(SUBSTRING(REVERSE(@long_str_Tmp),
1,
@long_str_Tmp_LEN
- @split_str_Position_END))
IF @short_str <> ''
INSERT INTO @tmp
SELECT @short_str
END
END
RETURN
END
用游标循环获取Split的值
DECLARE cur_user CURSOR FOR
SELECT short_str FROM dbo.split(@UserIDs,',')
OPEN cur_user
FETCH NEXT FROM cur_user INTO @userId
WHILE @@fetch_status = 0
BEGIN
INSERT INTO #User
SELECT UserId ,
MO_StaffID ,
UserName
FROM dbo.aspnet_Users
WHERE UserId = @userId ;
FETCH NEXT FROM cur_user INTO @userId
END
CLOSE cur_user
DEALLOCATE cur_user