varchar 变量中包含的整数值字符串在存储过程的 HAVING...IN 子句中不起作用

发布于 2024-11-18 01:14:35 字数 390 浏览 2 评论 0原文

下面是存储过程中的语句示例：

  SELECT @nDBNum = Num
    FROM Num_members
GROUP BY Num
  HAVING SUM(CASE WHEN Part_No IN (@strOrderedString) THEN 1 ELSE 0 END) = @nCount
     AND COUNT(*) = @nCount

如果“IN”子句中的变量@strOrderedString 只有一个数字，则一切正常。但是，如果此字符串具有逗号分隔数字列表，则执行存储过程时会出现语法错误（例如：将 varchar 值 '1259,2423,2701,2415,2453' 转换为数据类型 int 的列时出现语法错误.)

我怎样才能让这个查询工作？

原文

Here's an example of the statement in the stored procedure:

  SELECT @nDBNum = Num
    FROM Num_members
GROUP BY Num
  HAVING SUM(CASE WHEN Part_No IN (@strOrderedString) THEN 1 ELSE 0 END) = @nCount
     AND COUNT(*) = @nCount

If the variable @strOrderedString in the "IN" clause has only one number things work fine. However, if this string has a list of comma delimited numbers I get a syntax error from execution of the stored procedure (ex: Syntax error converting the varchar value '1259,2423,2701,2415,2453' to a column of data type int.)

How can I get this query to work?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

浅笑轻吟梦一曲 2024-11-25 01:14:35

您会收到错误，因为 SQL 不支持用变量来表示 IN 子句的逗号分隔值列表 - 它需要每个值一个变量，并在 SQL 中定义逗号。您需要使用动态 SQL，其中 SQL 是在执行之前连接的字符串，以便您发布的内容正常工作。

由于您使用的是 SQL Server 2000，因此需要使用 EXEC/EXECUTE执行动态 SQL。 sp_executesql 是 SQL Server 2005+。

回复收藏 0 原文

谈下烟灰 2024-11-25 01:14:35

您可以将这些值作为 XML 参数传递给存储过程。

<Ids>
    <Id>1</Id>
    <Id>2</Id>
    <Id>3</Id>
    <Id>4</Id>
</Ids>

并将其分配给表变量；

DECLARE @IdsToSearch table(id int);
INSERT INTO @IdsToSearch (id)
SELECT Ids.Id.value('.','INT') FROM @xml.nodes('/Ids/Id') as Ids(Id)

然后可以在您的 sql 中使用它；

SELECT @nDBNum = Num
    FROM Num_members
GROUP BY Num
  HAVING SUM(CASE WHEN Part_No IN (SELECT id FROM @IdsToSearch) THEN 1 ELSE 0 END) = @nCount
     AND COUNT(*) = @nCount

这假设您使用的是 Sql Server 2005/2008

You could pass the values as an XML parameter to the stored procedure.

<Ids>
    <Id>1</Id>
    <Id>2</Id>
    <Id>3</Id>
    <Id>4</Id>
</Ids>

and assign this to a Table Variable;

DECLARE @IdsToSearch table(id int);
INSERT INTO @IdsToSearch (id)
SELECT Ids.Id.value('.','INT') FROM @xml.nodes('/Ids/Id') as Ids(Id)

and this can then be used in your sql;

SELECT @nDBNum = Num
    FROM Num_members
GROUP BY Num
  HAVING SUM(CASE WHEN Part_No IN (SELECT id FROM @IdsToSearch) THEN 1 ELSE 0 END) = @nCount
     AND COUNT(*) = @nCount

This assumes you are using Sql Server 2005/2008

回复收藏 0 原文

不知所踪 2024-11-25 01:14:35

您可以获取输入字符串并调用表值函数将其转换为可以嵌套到 IN 子句中的单列表。比使用动态 sql 更好。

CREATE FUNCTION [dbo].[ConvertCSVToTable] ( @StringInput VARCHAR(8000) )
RETURNS @OutputTable TABLE ( Id int )
AS
BEGIN

    DECLARE @String    VARCHAR(10)

    WHILE LEN(@StringInput) > 0
    BEGIN
        SET @String      = LEFT(@StringInput, 
                                ISNULL(NULLIF(CHARINDEX(',', @StringInput) - 1, -1),
                                LEN(@StringInput)))
        SET @StringInput = SUBSTRING(@StringInput,
                                     ISNULL(NULLIF(CHARINDEX(',', @StringInput), 0),
                                     LEN(@StringInput)) + 1, LEN(@StringInput))

        INSERT INTO @OutputTable ( Id )
        VALUES ( @String )
    END

    RETURN
END

这将允许您仍然参数化列表输入并避免动态 sql，结果看起来像......

    SELECT @nDBNum = Num    FROM Num_membersGROUP BY Num  
    HAVING SUM(CASE WHEN Part_No IN (
SELECT Id from dbo.ConvertCSVToTable(@strOrderedString)
) 
    THEN 1 ELSE 0 END) = @nCount
    AND COUNT(*) = @nCount

You can take your input string and call a table valued function to convert it to a single column table you can nest into the IN clause. Better than using dynamic sql.

CREATE FUNCTION [dbo].[ConvertCSVToTable] ( @StringInput VARCHAR(8000) )
RETURNS @OutputTable TABLE ( Id int )
AS
BEGIN

    DECLARE @String    VARCHAR(10)

    WHILE LEN(@StringInput) > 0
    BEGIN
        SET @String      = LEFT(@StringInput, 
                                ISNULL(NULLIF(CHARINDEX(',', @StringInput) - 1, -1),
                                LEN(@StringInput)))
        SET @StringInput = SUBSTRING(@StringInput,
                                     ISNULL(NULLIF(CHARINDEX(',', @StringInput), 0),
                                     LEN(@StringInput)) + 1, LEN(@StringInput))

        INSERT INTO @OutputTable ( Id )
        VALUES ( @String )
    END

    RETURN
END

This would allow you to still parameterize your list input and avoid dynamic sql, the result would look something like...

    SELECT @nDBNum = Num    FROM Num_membersGROUP BY Num  
    HAVING SUM(CASE WHEN Part_No IN (
SELECT Id from dbo.ConvertCSVToTable(@strOrderedString)
) 
    THEN 1 ELSE 0 END) = @nCount
    AND COUNT(*) = @nCount

回复收藏 0 原文

~没有更多了~