大家都知道在sql中查询逗号分隔数据可以使用in语句,但在参数化存储过程语句中这种方法确实行不通,无论在参数数处传递多少逗号字符串都会被认为是一个值.

比如

ALTER PROCEDURE [dbo].[Admin_GetListByIds] @Ids VARCHAR(1000)
AS 
    BEGIN
        SET NOCOUNT ON
        SELECT  *
        FROM    dbo.[Admin] 
        WHER Id in (@Ids) 
    END

这里查询结果会为空

        SELECT  *
        FROM    dbo.[Admin] 
        WHER Id in (@Id1,@Id2) 

这样写才会执行,当然没人会写成这样,只是为方便理解.

本文要介绍的是使用表值函数的方法

--查询逗号字符串为数字的情况
--查询
ALTER PROCEDURE [dbo].[Admin_GetListByIds] @Ids VARCHAR(1000)
AS
BEGIN
	SET NOCOUNT ON

	SELECT *
	FROM dbo.[Admin] t1
	INNER JOIN dbo.Int32ToTable(@Ids, ',') t2 ON t1.Id = t2.Id
END

--删除
ALTER PROCEDURE [dbo].[Admin_DeleteByIds] (
	@Ids VARCHAR(200)
	)
AS
BEGIN
	SET NOCOUNT ON;

	DELETE
	FROM dbo.[Admin]
	FROM dbo.[Admin] AS A
	INNER JOIN Int32ToTable(@Ids, ',') AS B ON A.Id = B.Id;
END

--逗号分隔字符串为字符的情况
--查询
ALTER PROCEDURE [dbo].[Tags_GetListByDataTypeAndTagNames] (
	@DataType INT
	,@TagNames VARCHAR(1000) 
	)
AS
BEGIN
	SELECT a.*
	FROM dbo.Quiz_Tags AS A
	INNER JOIN StringToTable(@TagNames, ',') AS B ON A.TagName = B.Id
	WHERE DataType = @DataType
END

所用到的表值函数

/****** Object:  UserDefinedFunction [dbo].[Int32ToTable]    Script Date: 09/04/2014 13:11:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[Int32ToTable]
(
	@ids [nvarchar](max),
    @separator [char](1)
)
RETURNS @IdsTable TABLE 
(
   [Id] INT 
)
AS
BEGIN
	IF (RIGHT(@ids,1)=@separator)
	BEGIN
		SET @ids=SUBSTRING(@ids,0,LEN(@ids));
	END

	Set @ids= '<ids><im id="' + REPLACE(@ids,@separator,'"></im><im id="')+'"></im></ids>'
	
	DECLARE @xmlmode xml;
	SET @xmlmode=CAST(@ids as xml);
	

	INSERT INTO @IdsTable 
	SELECT T1.ids.value('@id','int') as [Id] 
	FROM @xmlmode.nodes('/ids/im') T1(ids)

	RETURN ;
END

/****** Object:  UserDefinedFunction [dbo].[StringToTable]    Script Date: 09/04/2014 13:12:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[StringToTable]
    (
      @ids [nvarchar](MAX) ,
      @separator [char](1)
    )
RETURNS @IdsTable TABLE ( [Id] VARCHAR(36) )
AS 
    BEGIN
        IF ( RIGHT(@ids, 1) = @separator ) 
            BEGIN
                SET @ids = SUBSTRING(@ids, 0, LEN(@ids));
            END

        SET @ids = '<ids><im id="' + REPLACE(@ids, @separator, '"></im><im id="')
            + '"></im></ids>'
	
        DECLARE @xmlmode XML;
        SET @xmlmode = CAST(@ids AS XML);
	

        INSERT  INTO @IdsTable
                SELECT  T1.ids.value('@id', 'nvarchar(36)') AS [Id]
                FROM    @xmlmode.nodes('/ids/im') T1 ( ids )

        RETURN;
    END

当然,你如果使用的是动态存储过程的化,也可以直接使用in,就不用这么麻烦了.