SQL Server String splitting function (Table value function)

-- ===================================================== ======
-- Author: <over>
-- Create Date: <2007/09/19>
-- Description: <string. Split () Split string>
-- ===================================================== ======
Alter function [DBO]. [split]
(
@ String varchar (255,
@ Separator char = ','
)
Returns @ temp table
(
Item int
)
As
Begin
Declare @ item int
Declare @ currentindex int
Declare @ nextindex int
Declare @ length int -- String Length
Set @ currentindex = 1
Set @ length = datalength (@ string)

If @ string is not null
Begin
While @ currentindex <@ Length
Begin
-- Charindex (substring, string to be searched)
Set @ nextindex = charindex (@ separator, @ string, @ currentindex)
Set @ item = substring (@ string, @ currentindex, @ nextindex-@ currentindex)
Set @ currentindex = @ nextindex + 1
-- Place the value of the temporary variable in the table to be returned
Insert into @ temp values (@ item)
End
End
Return
End

Call code:

-- ===================================================== ======
-- Author: <over>
-- Create Date: <2007/09/19>
-- Description: <update a user's role (change authorization)>
-- ===================================================== ======
Alter procedure DBO. upduserrole
@ Userid int, -- there is no check for userid
@ Roleids varchar (255) -- 1, 2, 3, 4, 5
As
Set nocount on

-- Single permission Value
Declare @ roleid int

-- Obtain the split string
Declare roles cursor
Select item from [DBO]. [split] (@ roleids, default)
-- For read only

-- Delete original Permissions
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)
-- Insert the next value to the variable @ roleid
Fetch next from Roles
Into @ roleid
End

Close roles

-- Deallocate is used to delete the prepared query.
-- If you do not explicitly deallocate a prepared query, it will be deleted at the end of the session.
Deallocate roles
-- Return

Note:
This function is called in the sqlserver2005 stored procedure. If you need to port it to sqlserver2000, you only need:

@ Separator char = ','

Replace it with @ separator char (1) = ','

You can refer to the SQL Server2000 Stored Procedure settings input parameter default article.