将逗号分割的字符串存入临时表，并用做查询条件

1.方法定义：

ALTER FUNCTION [dbo].[splitIds] (
    @String VARCHAR(MAX),
    @Delimiter VARCHAR(MAX)
) RETURNS @temptable TABLE (items VARCHAR(MAX)) AS
BEGIN
    DECLARE @idx INT=1
    DECLARE @slice VARCHAR(MAX) 
    IF LEN(@String) < 1 OR LEN(ISNULL(@String,'')) = 0
        RETURN
    WHILE @idx != 0
    BEGIN
        SET @idx = CHARINDEX(@Delimiter,@String)
        IF @idx != 0
            SET @slice = LEFT(@String,@idx - 1)
        ELSE
            SET @slice = @String
        IF LEN(@slice) > 0
            INSERT INTO @temptable(items) VALUES(@slice)
        SET @String = RIGHT (@String, LEN(@String) - @idx)
        IF LEN(@String) = 0
            BREAK
    END
    RETURN
END

2.使用方法

DECLARE @unitId nvarchar(256)
SET @unitId = '1,2,3,4,5,6'
--查询Id为1,2,3,4,5,6的数据
SELECT *
FROM History_Labs lab 

WHERE
	exists (SELECT * FROM splitIds(@unitId,',') where lab.Id = items)