blocks|key|429187|text|您需要拆分字符串并将其转储到临时表中。然后对临时表进行连接。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|429188|有很多这样的例子，这里有一个随机的例子。|429189|http://blogs.microsoft.co.il/blogs/itai/archive/2009/02/01/t-sql-split-function.aspx|offset|length|429190|entityMap|0|LINK|mutability|MUTABLE|url^0|0|0|0|2C|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|P|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|Q|8|@]|9|@$F|R|G|S|1|T]]|A|$]]|$1|H|3|-4|5|6|7|U|8|@]|9|@]|A|$]]]|I|$J|$5|K|L|M|A|$N|E]]]]

You need to split the string and dump it into a temp table. Then you join against the temp table.

There are many examples of this, here is one at random.

<a href="http://blogs.microsoft.co.il/blogs/itai/archive/2009/02/01/t-sql-split-function.aspx" rel="nofollow noreferrer">http://blogs.microsoft.co.il/blogs/itai/archive/2009/02/01/t-sql-split-function.aspx</a>

blocks|key|1728604|text|我会创建一个函数，它接受逗号分隔的字符串并将其拆分，然后返回一个单列表变量，每个值都在它自己的行中。从in语句的返回表中选择该列。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1728605|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

I would create a function that takes your comma delimited string and splits it and returns a single column table variable with each value in its own row. Select that column from the returned table in your IN statement.

blocks|key|326447|text|我发现了一种可爱的方法--但它有一点难闻。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|326448|declare+@delimitedlist+varchar(8000)
set+@delimitedlist+=+'%7C1%7C2%7C33%7C11%7C3134%7C'

select+*+from+mytable+where+@delimitedlist+like+'%25%7C'+%2B+cast(id+as+varchar)+%2B+'%7C%25'|code-block|syntax|javascript|326449|所以..。这将返回id等于1、2、33、11或3134的所有记录。|326450|SQL+：我还想补充说，这不容易受到注入的攻击(而动态依赖于您的白名单/黑名单技术来确保它不会受到攻击)。它可能会对大数据集造成性能影响，但它是有效的，而且是安全的。|offset|length|style|BOLD|326451|entityMap^0|0|0|0|I|9|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|R|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|S|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|T|8|@$K|U|L|V|M|N]]|9|@]|A|$]]|$1|O|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|P|$]]

I found a cute way of doing this - but it smells a bit.

<pre><code>declare @delimitedlist varchar(8000)
set @delimitedlist = '|1|2|33|11|3134|'

select * from mytable where @delimitedlist like '%|' + cast(id as varchar) + '|%'
</code></pre>

So... this will return all records with an id equal to 1, 2, 33, 11, or 3134.

EDIT:
I would also add that this is not vulnerable to SQL injection (whereas dynamic SQL relies on your whitelisting/blacklisting techniques to ensure it isn't vulnerable). It might have a performance hit on large sets of data, but it works and it's secure.

blocks|key|429246|text|如果没有拆分函数，则如下所示：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|429247|CREATE+PROCEDURE+[dbo].[myStoredProcedure]
++++@myFilter+varchar(512)+=+NULL+--+don't+use+NVARCHAR+for+a+list+of+INTs
AS
SET+NOCOUNT+ON
BEGIN
++++SELECT
++++++++t.ID,
++++++++t.Name++++++++++
++++FROM
++++++++MyTable+t
++++WHERE
++++++++CHARINDEX(','%2BCONVERT(VARCHAR,t.ID)%2B',',@myFilter)+>+0
++++ORDER+BY
++++++++t.Name
END|code-block|syntax|javascript|429248|性能将会很差。每次扫描一次表。最好使用拆分函数。请参阅：http://www.sommarskog.se/arrays-in-sql.html|offset|length|429249|entityMap|0|LINK|mutability|MUTABLE|url|http://www.sommarskog.se/arrays-in-sql.html^0|0|0|S|17|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|T|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|U|8|@]|9|@$I|V|J|W|1|X]]|A|$]]|$1|K|3|-4|5|6|7|Y|8|@]|9|@]|A|$]]]|L|$M|$5|N|O|P|A|$Q|R]]]]

Absent a split function, something like this:

<pre><code>CREATE PROCEDURE [dbo].[myStoredProcedure]
 @myFilter varchar(512) = NULL -- don't use NVARCHAR for a list of INTs
AS
SET NOCOUNT ON
BEGIN
 SELECT
 t.ID,
 t.Name 
 FROM
 MyTable t
 WHERE
 CHARINDEX(','+CONVERT(VARCHAR,t.ID)+',',@myFilter) &gt; 0
 ORDER BY
 t.Name
END
</code></pre>

Performance will be poor. A table scan every time. Better to use a split function. See: <a href="http://www.sommarskog.se/arrays-in-sql.html" rel="nofollow noreferrer">http://www.sommarskog.se/arrays-in-sql.html</a>

blocks|key|429265|text|你可以做一个函数，它接受你的参数，滑动它，并返回包含所有数字的表。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|429266|如果您正在使用SQL+Server中的列表或数组，我建议您阅读Erland+Sommarskog的精彩文章：|429267|Arrays+and+Lists+in+SQL+Server+2005|offset|length|429268|entityMap|0|LINK|mutability|MUTABLE|url|http://www.sommarskog.se/arrays-in-sql-2005.html^0|0|0|0|Z|0|0^^$0|@$1|2|3|4|5|6|7|P|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|Q|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|R|8|@]|9|@$F|S|G|T|1|U]]|A|$]]|$1|H|3|-4|5|6|7|V|8|@]|9|@]|A|$]]]|I|$J|$5|K|L|M|A|$N|O]]]]

You could make function that takes your parameter, slipts it and returns table with all the numbers in it.

If your are working with lists or arrays in SQL Server, I recommend that you read Erland Sommarskogs wonderful stuff:

<a href="http://www.sommarskog.se/arrays-in-sql-2005.html" rel="nofollow noreferrer">Arrays and Lists in SQL Server 2005</a>

blocks|key|1733432|text|我也有几篇关于这方面的博客文章，有很多有趣的后续评论和对话：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1733433|More+on+splitting+lists|offset|length|1733434|Processing+list+of+integers|1733435|entityMap|0|LINK|mutability|MUTABLE|url|https://sqlblog.org/2009/08/06/more-on-splitting-lists-custom-delimiters-preventing-duplicates-and-maintaining-order|1|https://sqlblog.org/2009/08/01/processing-a-list-of-integers-my-approach^0|0|0|N|0|0|0|R|1|0^^$0|@$1|2|3|4|5|6|7|R|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|S|8|@]|9|@$D|T|E|U|1|V]]|A|$]]|$1|F|3|G|5|6|7|W|8|@]|9|@$D|X|E|Y|1|Z]]|A|$]]|$1|H|3|-4|5|6|7|10|8|@]|9|@]|A|$]]]|I|$J|$5|K|L|M|A|$N|O]]|P|$5|K|L|M|A|$N|Q]]]]

I have a couple of blog posts on this as well, with a lot of interesting followup comments and dialog:
<a href="https://sqlblog.org/2009/08/06/more-on-splitting-lists-custom-delimiters-preventing-duplicates-and-maintaining-order" rel="nofollow noreferrer">More on splitting lists</a>
<a href="https://sqlblog.org/2009/08/01/processing-a-list-of-integers-my-approach" rel="nofollow noreferrer">Processing list of integers</a>

I am passing a comma-delimited list of values into a stored procedure. I need to execute a query to see if the ID of an entity is in the comma-delimited list. Unfortunately, I think I do not understand something. 

When I execute the following stored procedure:

<pre><code>exec dbo.myStoredProcedure @myFilter=N'1, 2, 3, 4'
</code></pre>

I receive the following error:

<pre><code>"Conversion failed when converting the varchar value '1, 2, 3, 4' to data type int."
</code></pre>

My stored procedure is fairly basic. It looks like this:

<pre><code>CREATE PROCEDURE [dbo].[myStoredProcedure]
 @myFilter nvarchar(512) = NULL
AS
SET NOCOUNT ON
BEGIN
 -- Remove the quote marks so the filter will work with the "IN" statement
 SELECT @myFilter = REPLACE(@myFilter, '''', '')

 -- Execute the query
 SELECT
 t.ID,
 t.Name 
 FROM
 MyTable t
 WHERE
 t.ID IN (@myFilter)
 ORDER BY
 t.Name
END
</code></pre>

How do I use a parameter in a SQL statement as described above? Thank you!

SQL Server - Replacing Single Quotes and Using IN

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我正在将一个逗号分隔的值列表传递给一个存储过程。我需要执行一个查询，看看某个实体的ID是否在逗号分隔的列表中。不幸的是，我想我不明白一些事情。当我执行以下存储过程时：exec dbo.myStoredProcedure @myFilter=N'1, 2, 3, 4'我收到以下错误："Conversion failed ...

问SQL Server -替换单引号并使用IN
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问SQL Server -替换单引号并使用INEN