帮助使用 int 的 TSQL IN 语句 [英] help with TSQL IN statement with int

问题描述

我正在尝试在存储过程中创建以下 select 语句

I am trying to create the following select statement in a stored proc

@dealerids nvarchar(256)

SELECT * 
FROM INVOICES as I
WHERE convert(nvarchar(20), I.DealerID) in (@dealerids)

I.DealerID 是表中的一个 INT.并且经销商的参数将被格式化为(8820, 8891, 8834)

I.DealerID is an INT in the table. and the Parameter for dealerids would be formatted such as (8820, 8891, 8834)

当我使用提供的参数运行它时，我没有返回任何行.我知道这些dealerIDs应该提供行，就好像我单独做一样，我得到了我的期望.我想我在做

When I run this with parameters provided I get no rows back. I know these dealerIDs should provided rows as if I do it individually I get back what I expect. I think I am doing

   WHERE convert(nvarchar(20), I.DealerID) in (@dealerids)

不正确.谁能指出我在这里做错了什么?

incorrectly. Can anyone point out what I am doing wrong here?

推荐答案

你不能那样使用 @dealerids，你需要使用动态 SQL，像这样:

You can't use @dealerids like that, you need to use dynamic SQL, like this:

@dealerids nvarchar(256) 

EXEC('SELECT *  
FROM INVOICES as I 
WHERE convert(nvarchar(20), I.DealerID) in (' + @dealerids + ')'

缺点是，除非您专门控制进入 @dealerids 的数据，否则您会面临 SQL 注入攻击.

The downside is that you open yourself up to SQL injection attacks unless you specifically control the data going into @dealerids.

根据您的 SQL Server 版本，有更好的方法来处理这个问题，这些方法记录在 这篇很棒的文章.

There are better ways to handle this depending on your version of SQL Server, which are documented in this great article.

这篇关于帮助使用 int 的 TSQL IN 语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！