Combining IN and Where query SQL

Question

 StrSqlLines = "Select * From dbo.SqlDtaToCreate WHERE DtaLineAccountToDebit = '" + Straccref + "' and DtaLineCode IN('" + joined + "')";

I am getting an error when i execute this query

Conversion failed when converting the varchar value '116743,116744,116745' to data type int.

However the query works fine from SQL

select * from SqlDtaToCreate where DtaLineAccountToDebit='123.567U' and DtaLineCode IN('116745','116746','116747')

@araknoid I definitely think this is SQL Server, not Oracle. — ppeterka, Feb 28 '13 at 11:26
@ppeterka My fault, got the link from he incorrect SQL Bookmark folder... :) This is for SQL Server... http://dirk.net/2007/05/07/split-like-string-to-array-function-in-transact-sql/ — araknoid, Feb 28 '13 at 11:32

ppeterka · Accepted Answer · 2013-02-28T11:22:31.920

First:

This might be a bad case of SQL injection! Use prepared statements (or at least proper escaping)!

Second:

Your immediate problem is the enclosing ' for the IN clause correctly:

 StrSqlLines = "Select * " +
               "From dbo.SqlDtaToCreate "
               " WHERE DtaLineAccountToDebit = '" + Straccref + "' " + 
               " and DtaLineCode IN(" + joined + ")"; //notice missing ' characters

Anything enclosed by ' characters is trated as a single string. SQL server tried to parse this single string as a number, but as it was not parseable as that, it reported the error.

Third:

When using numeric data, never ever ever, never, ever (did I mention never yet?) use textual data to compare it with - that can literally kill performance. (at this scale, of course this is not significant, but keeping this in mind can save a lot of unnecessary performance analysis and debugging...)

So while this query actually works:

select * from SqlDtaToCreate where DtaLineAccountToDebit='123.567U' and DtaLineCode IN('116745','116746','116747')

It does implicit conversion of the data supplied, so the proper way is to:

select * from SqlDtaToCreate where DtaLineAccountToDebit='123.567U' and DtaLineCode IN(116745,116746,116747)

score 0 · Answer 2 · answered Feb 28 '13 at 11:16

0

Notice missing ' in your joined variable.

'116743,116744,116745'

'116745','116746','116747'

Ideally, since you're comparing INTs all you need is

116743,116744,116745

And read about sql injection.

answered Feb 28 '13 at 11:16

Jakub Konecki

45,581
7
87
126

I think OP has int values to compare to... And he incorrectly uses textual data to compare it to, implying data conversion – ppeterka Feb 28 '13 at 11:17
Yes, (116745,116746,116747) will be enough, but Sql Server will convert strings anyway – Jakub Konecki Feb 28 '13 at 11:18

score 0 · Answer 3 · answered Feb 28 '13 at 11:17

Its the conversion on the term joined, it seems the Database server can convert a number contained in quotes but not more than on esparated by comma, in your case:

'116743,116744,116745'

To :

'116745','116746','116747'

Try to reformat joined to separate the numbers by quotes., or remove the quotes from the sql statament so you get IN(" + joined + ")" instead of: IN('" + joined + "')"

score 0 · Answer 4 · answered Feb 28 '13 at 11:17

Note that your two queries are not the same. The programmatic one puts single quotes around the entire group of numbers, while your SQL test puts single quotes around each number.

Probably you can just omit quotes altogether, though:

StrSqlLines = "Select * From dbo.SqlDtaToCreate WHERE DtaLineAccountToDebit = '" 
   + Straccref + "' and DtaLineCode IN(" + joined + ")";

Combining IN and Where query SQL

4 Answers4

First:

Second:

Third: