WHERE ... IN ... SQL Server 存储过程的问题

Question

我必须在存储过程中实现 SELECT ... FROM ... WHERE .. IN 查询。

下面是我的存储过程中的代码

ALTER PROCEDURE [dbo].[SP_GetQuestionSetMultiCat] 
    -- Add the parameters for the stored procedure here
    @PIN varchar(50),
    @CatIds varchar(50),
    @Range int,
    @Que_Type varchar(50)

AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    declare @qtId as int;
    select @qtId = Que_Type_Id from dbo.QuestionType_Tbl where Que_Type=@Que_Type;


    -- Insert statements for procedure here

        Select Top(@Range) 
            QId,
            Que_Type_Id,
            Que_Level_Id,
            Que_Category_Id, 
            Que,
            Opt1,
            Opt2,
            Opt3,
            Opt4,
            Ans 
        From 
            dbo.Que_Tbl 
        Where 
            (Que_Category_Id in (cast(@CatIds as varchar)))
            and (Que_Type_Id=@qtId) 
            and (Qid not in (Select Que_Id From dbo.UserQuestion_Mapping where PIN=@PIN and Que_typeID=@qtId))


END

---

查看 where 条件。 Que_Category_Id是int类型。我想要执行的是 -

Where Que_Category_Id in (1,2,3,4)

我传递的 in 值是从我的 C# 代码转换而来的字符串。

当我执行此查询时，例如 -

exec SP_GetQuestionSetMultiCat '666777','4,5,6',5,'Practice'

它生成错误 -

将 varchar 值“{4,5,6}”转换为数据类型 int 时转换失败。

谁能帮我解决这个问题。

感谢您分享您的宝贵时间。

Answer 1

1)将varchar值'{4,5,6}'转换为int数据类型时转换失败。

此错误的原因是数据类型优先级。 INT 数据类型的优先级高于 VARCHAR 数据类型（16-INT 与 27-VARCHAR）。
因此，SQL Server 尝试将 '{4,5,6}' 转换为 INT，反之亦然。

2) 相反，我会将 @CatIds 转换为 XML，然后使用 nodes(...) 方法:

SET ANSI_WARNINGS ON;

DECLARE @CatIds VARCHAR(50) = '4,5,6';

DECLARE @x XML;
SET     @x = '<node>' + REPLACE(@CatIds, ',', '</node> <node>') + '</node>';
DECLARE @IDs TABLE
(
    ID INT PRIMARY KEY
);
INSERT  @IDs(ID)
SELECT  t.c.value('.', 'INT')
FROM    @x.nodes('/node') t(c);

--Test    
SELECT  *
FROM    @IDs

3) 下一步是使用 重写查询在（从中选择ID @IDs) 而不是 in (cast(@CatIds as varchar))：

SET ANSI_WARNINGS ON;

ALTER PROCEDURE [dbo].[SP_GetQuestionSetMultiCat] 
    -- Add the parameters for the stored procedure here
    @PIN varchar(50),
    @CatIds varchar(50),
    @Range int,
    @Que_Type varchar(50)

AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    declare @qtId as int;
    select @qtId = Que_Type_Id from dbo.QuestionType_Tbl where Que_Type=@Que_Type;

    --Start: New T-SQL code
    DECLARE @x XML;
    SET     @x = '<node>' + REPLACE(@CatIds, ',', '</node> <node>') + '</node>';
    DECLARE @IDs TABLE
    (
        ID INT PRIMARY KEY
    );
    INSERT  @IDs(ID)
    SELECT  t.c.value('.', 'INT')
    FROM    @x.nodes('/node') t(c);
    --End

    -- Insert statements for procedure here

        Select Top(@Range) 
            QId,
            Que_Type_Id,
            Que_Level_Id,
            Que_Category_Id, 
            Que,
            Opt1,
            Opt2,
            Opt3,
            Opt4,
            Ans 
        From 
            dbo.Que_Tbl 
        Where 
            --The search condition is rewritten using IN(subquery)
            Que_Category_Id in (SELECT ID FROM @IDs)
            and (Que_Type_Id=@qtId) 
            and (Qid not in (Select Que_Id From dbo.UserQuestion_Mapping where PIN=@PIN and Que_typeID=@qtId))


END

4) 调用存储过程：

exec SP_GetQuestionSetMultiCat '666777','4,5,6',5,'Practice'