創(chuàng)建函數(shù)：
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[f_split]') AND xtype IN (N'FN',N'IF',N'TF'))
DROP FUNCTION [dbo].[f_split]
go
CREATE FUNCTION f_split(
@str NVARCHAR(MAX),
@separtor VARCHAR(10)
)
RETURNS @temp TABLE( VALUE NVARCHAR(4000))
AS
BEGIN
DECLARE @i INT
SET @str=RTRIM(LTRIM(@str))
SET @i=charindex(@separtor,@str)
WHILE @i>=1
BEGIN
INSERT @temp VALUES(LEFT(@str,@i-1))
SET @str=SUBSTRING(@str,@i+1,len(@str)-@i)
SET @i=charindex(@separtor,@str)
END
IF @str<>''
INSERT @temp VALUES(@str)
RETURN
END
GO
調(diào)用函數(shù)：
SELECT @strcount= COUNT(*) FROM f_split(@ProcedureIds,',')
SET @i=0;
WHILE @i<@strcount
BEGIN
WITH tabs AS (SELECT ROW_NUMBER() over(ORDER BY VALUE) AS ROWS,* FROM f_split(@ProcedureIds,',') )SELECT @ProcedureId=VALUE FROM tabs WHERE ROWS=(@i+1)
END
