如何在存储过程中的查询中传递逗号分隔的值 [英] How to pass a comma separated value to in query in stored procedure

问题描述

大家好

我有一个具有100条delete语句的存储过程.
我需要在delete语句的"in子句"中传递逗号分隔的值.该怎么做?发生错误，例如Varchar值无法转换为int.而且我也不希望解决方案出现在子查询中.
它应该在单个变量中.

Hi All

I am having a stored procedure which has 100 delete statements.
I need to pass the comma separated value in "in clause" of delete statement.how to do that? The error occurs like Varchar value cannot be converted to int.And also i don''t want the solution to be in sub query.
It should be in a single variable.

DELETE FROM managed_component_logical_storage
WHERE surrogate_disk_subsystem_id
IN
(
    SELECT surrogate_disk_subsystem_id
    FROM managed_component_physical_storage
    WHERE managed_system_id in (@device_category)
)

DELETE FROM managed_component_physical_storage  WHERE managed_system_id in (@device_category)


SELECT SUBSTRING(
(SELECT '','' + s.Name
FROM HumanResources.Shift s
ORDER BY s.Name
FOR XML PATH('''')),2,200000) AS CSV

这个查询给了我结果.但是我希望单个变量具有相同的结果.在"in子句"中替换变量后，它不应引发错误.
有什么想法吗?

This query gives me the result. But I want the same result in a single variable.After replacing the variable in "in clause" it should not throw error.
Any ideas please?

推荐答案

查看有关堆栈溢出的讨论- ^ ]

它讨论了解决同一问题的可能方法.

Check out this discussion on Stack Overflow - SQL use comma-separated values with IN clause[^]

It discusses about a possible solution to the same issue.

要删除IN子句，请在表上使用JOIN.
请参见此处 [

To remove the IN clause use a JOIN on the tables.
See here[^].

这篇关于如何在存储过程中的查询中传递逗号分隔的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！