代码之家 › 专栏 › 技术社区 › user70192

SQL Server-替换单引号并在中使用

tsql sql-server sql

user70192 · 技术社区 · 15 年前

我正在将以逗号分隔的值列表传递到存储过程中。我需要执行一个查询,看看实体的ID是否在逗号分隔的列表中。不幸的是,我想我不明白什么。

当我执行以下存储过程时:

exec dbo.myStoredProcedure @myFilter=N'1, 2, 3, 4'

我收到以下错误:

"Conversion failed when converting the varchar value '1, 2, 3, 4' to data type int."

我的存储过程相当基本。看起来是这样的:

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

如何在上面描述的SQL语句中使用参数?谢谢您!

6 回复 | 直到 13 年前

pirho 15 年前

您可以使函数接受您的参数,滑动它并返回包含所有数字的表。

如果您使用的是SQL Server中的列表或数组,我建议您阅读Erland Sommarskogs的精彩文章:

Arrays and Lists in SQL Server 2005

Jonathan Allen 15 年前

您需要拆分字符串并将其转储到临时表中。然后加入临时表。

有很多这样的例子,这里是一个随机的。

http://blogs.microsoft.co.il/blogs/itai/archive/2009/02/01/t-sql-split-function.aspx

Peter Radocchia 15 年前

缺少拆分函数,如下所示:

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) > 0
    ORDER BY
        t.Name
END

性能会很差。每次扫描一张桌子。最好使用拆分函数。见: http://www.sommarskog.se/arrays-in-sql.html

John Lechowicz 15 年前

我将创建一个函数,该函数接受逗号分隔的字符串并将其拆分,并返回一个单列表变量,每个值都在自己的行中。从IN语句中返回的表中选择该列。

Mayo 15 年前

我找到了一个很可爱的方法——但闻起来有点难闻。

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

select * from mytable where @delimitedlist like '%|' + cast(id as varchar) + '|%'

所以…这将返回ID为1、2、33、11或3134的所有记录。

编辑 : 我还想补充一点,这并不容易被SQL注入攻击(而动态SQL依赖于您的白名单/黑名单技术来确保它不易受攻击)。它可能会对大型数据集产生性能影响,但它可以工作并且是安全的。

Aaron Bertrand 13 年前

我也有一些关于这个的博客文章,有很多有趣的后续评论和对话: