关于IN 后面传入ID列表参数表值函数的一个写法：非拼SQL

USE [DBName]
GO
/****** Object:  UserDefinedFunction [ISIS].[SplitIDs]    Script Date: 03/30/2011 14:49:55 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [ISIS].[SplitIDs]
(
 @IDList nvarchar(4000)
)
RETURNS
@ParsedList table (ID int)
AS
BEGIN
            DECLARE @ID nvarchar(10), @Pos int

                  SET @IDList = LTRIM(RTRIM(@IDList))+ ','

            SET @Pos = CHARINDEX(',', @IDList, 1)

            IF REPLACE(@IDList, ',', '') <> ''
            BEGIN
                        WHILE @Pos > 0
                        BEGIN
                                    SET @ID = REPLACE(LTRIM(RTRIM(LEFT(@IDList, @Pos - 1))), '''', '')
                                    IF @ID <> ''
                                    BEGIN
                                                INSERT INTO @ParsedList (ID)
                                                VALUES (CAST(@ID AS int)) --Use Appropriate conversion
                                    END
                                    SET @IDList = RIGHT(@IDList, LEN(@IDList) - @Pos)
                                    SET @Pos = CHARINDEX(',', @IDList, 1)
                        END
            END

            RETURN
END

 

调用方法：

FindID IN (select ID from ISIS.SplitIDs(@IDs))