如何在存储过程中的查询中传递逗号分隔的值

问题描述：

大家好

我有一个具有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[^].

如何在存储过程中的查询中传递逗号分隔的值

相关推荐