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 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


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

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