ASP.net simple delete query, pass in string value

Question

DELETE FROM tblArtworkApprovalUsers
WHERE     (userID NOT IN (@UserIDList)) AND (approvalID =
                          (SELECT     ID
                            FROM          tblArtworkApprovals
                            WHERE      (templateID = @TemplateID)))

This is in my table adapter. @UserIDList needs to accept something like:

2,44,12,70

How can I make this query accept that string?

score 2 · Accepted Answer · edited May 23 '17 at 11:55

NOT IN <expr> requires an expression and not a string. So if you are passing the parameter and not constructing the SQL dynamically this cannot be done.

Alternative is to create the SQL dynamically (while being aware of SQL Injection):

string commad = @"DELETE FROM tblArtworkApprovalUsers " +
             "WHERE (userID NOT IN ({0})) AND (approvalID ="+
                          "(SELECT     ID " +
                            "FROM          tblArtworkApprovals " +
                            "WHERE      (templateID = {1})))";

command = string.Format(command, userIDList, templateID);

UPDATE

Craig pointed to a better solution here which does not provide much better performance (since parameters are variable and query plan does not get cached unless it is exactly the same) but help with SQL injection attack: Parameterize an SQL IN clause

Thanks! How do I pass in a command to a table adapter though? — Tom Gullen, Mar 10 '11 at 12:12
Here's how to parameterize an IN clause, I assume same holds true for NOT IN: http://stackoverflow.com/questions/337704/parameterizing-a-sql-in-clause/337792#337792 — Craig, Mar 10 '11 at 12:12

score 1 · Answer 2 · answered Mar 10 '11 at 12:14

You have a couple of options...

Dynamically create the whole SQL string and execute (like Aliostad suggests).
Write a stored procedure that accepts the string, parses it into a temporary table, then run your query against that temp table. A quick search will provide many ways to do this (here is one).

score 1 · Answer 3 · answered Mar 10 '11 at 12:19

1

You might want to have a look at Arrays in SQL2005 and Arrays in SQL2008, depending on the version of your SQL server.

answered Mar 10 '11 at 12:19

Gorgsenegger

7,356
4
51
89

ASP.net simple delete query, pass in string value

3 Answers3

UPDATE