1

I'm trying to create a DELETE statement where I'll delete some items from a table based on a list of ids, using the IN clause:

DELETE FROM table WHERE id IN (@ids)

Using a SqlCommand, I'm doing the following to generate the IN parameter:

public void DeleteItemsByIds(int[] ids) {

    Parameters["ids"] = string.Join(", ", ids);
    ExecuteNonQuery(deleteStatement, Parameters);
}

Then I'm getting an error:

Error converting data type varchar to bigint

Someone know how could I solve it?

Thank you all!

Kiwanax
  • 1,265
  • 1
  • 22
  • 41
  • 1
    Well, the `IN` expects a **list of (presumably) `BIGINT`** values, while what you're providing is really just **a single string** ..... For SQL Server **2008** and newer, check out **table-valued parameters** to pass multiple values to a SQL statement – marc_s Mar 08 '15 at 20:39
  • Construct the entire SQL statement as text, not just the inclusion set, and that should work. – Pieter Geerkens Mar 08 '15 at 20:41
  • 1
    @PieterGeerkens: that is **horrible** advice as it opens up the possibility of **SQL injection** attacks... seriously - don't do this! – marc_s Mar 08 '15 at 20:46
  • possible duplicate of [T-SQL stored procedure that accepts multiple Id values](http://stackoverflow.com/questions/43249/t-sql-stored-procedure-that-accepts-multiple-id-values) – Paolo Mar 08 '15 at 20:49
  • have a look at [this](http://stackoverflow.com/questions/43249/t-sql-stored-procedure-that-accepts-multiple-id-values) SO answer. it is tailored on stored procedures but the proposed solutions are suitable for your situation. moreover you may get some hint pushing you to a stored procedure instead of a standalone (dynamic sql?) delete statement... – Paolo Mar 08 '15 at 20:52

2 Answers2

3

in expects a list of things whose types on the left and right side are compatible. Clearly the left side of the in is numeric and the right side is a string. And, you cannot convert the string to a number, because it is a comma-delimited list.

The fast and dirty solution is to simply use like:

DELETE
    FROM table
    WHERE ',' + @ids + ',' LIKE '%,' + CAST(id as VARCHAR(255)) + ',%';

However, this is suboptimal in terms of performance, because it cannot make use of an index on id. Better solutions requiring knowing more about the application that is using the SQL.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

this will definitely help you

select * from tblusers where userid in(select value from String_Split('1,2,3,4',','))

String_Split is an inbuilt SQL function

Rinku Choudhary
  • 1,529
  • 1
  • 13
  • 22