Click here to Skip to main content
15,942,934 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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 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
    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 ',' + s.Name
FROM HumanResources.Shift s
FOR XML PATH('')),2,200000) AS CSV

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?
Updated 14-Feb-11 22:18pm

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.
Share this answer
To remove the IN clause use a JOIN on the tables.
See here[^].
Share this answer

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900