0

While passing a storelist from C# code to a SQL query, I get this error:

Msg 245, Level 16, State 1, Line 5
Conversion failed when converting the varchar value '1,2' to data type int.

Please help me to resolve this.

declare @STORENUM Varchar(100)
set @STORENUM = '1,2'
print @STORENUM

SELECT DISTINCT 
    STORE_NUM, 
    ISNULL(LINE_1_ADDR, '') AS LINE_1_ADDR, 
    ISNULL(LINE_2_ADDR, '') AS LINE_2_ADDR, 
    ISNULL(LINE_3_ADDR, '') AS LINE_3_ADDR,
    CITY_NM, STATE_CD 
FROM 
    Store 
WHERE 
    STORE_NUM IN (CAST(@STORENUM AS INT))
marc_s
  • 675,133
  • 158
  • 1,253
  • 1,388
user1155576
  • 37
  • 1
  • 7

3 Answers3

1

The string '1,2' is not an integer. You cannot use IN on it.

You have two choices. Stuff the value into the string. SQL doesn't support parameters for IN lists.

Or, use LIKE:

WHERE ',' + @STORENUMS + ',' LIKE '%,' + @CAST(STORE_NUM as VARCHAR(255)) + ',%'
Gordon Linoff
  • 1,122,135
  • 50
  • 484
  • 624
0

I recomend you to do this that way:

  1. You had to split your varchar.
    How do I split a string so I can access item x?

  2. Then you can use IN on (select * from table)

Community
  • 1
  • 1
JustSomeNewbie
  • 115
  • 1
  • 11
  • Exists would be better than IN if you use this pattern you allude to. Because if a NULL is possible in the SELECT * FROM table... you will not get the results you want. But if you are going to split the string you can also simple join the tables as well – Matt Aug 06 '16 at 19:27
  • Since he declare this value I assume that tables will have expected output. – JustSomeNewbie Aug 06 '16 at 19:29
  • Is there a way to remove the single quotes ? If there are no single quotes in @STORENUM variable, i'm getting the result – user1155576 Aug 06 '16 at 19:31
  • I tried this. but it didn't work. I got the same error. I'm not sure how my storelist is having single qoutes – user1155576 Aug 06 '16 at 19:33
  • Are you sure you do it like : Replace(@STORENUM,'''',''). I got '''' there, not '''. Just do this replace and then select * from @Storenum and show your output. – JustSomeNewbie Aug 06 '16 at 19:36
  • Replace gave me 1,2. But in my where clause is this way -- WHERE STORE_NUM in (@STORENUM). This is giving me the same error. – user1155576 Aug 06 '16 at 19:42
  • WHERE STORE_NUM in (1,2) --> Works But WHERE STORE_NUM in ('1,2') --> Gives me an error – user1155576 Aug 06 '16 at 19:48
  • Cause even when u do this variable will always be treat with '' in query. I recomend you to do as i said in first post. U had to split string, then insert it into table and then select this from table in main query and it will work correctly. – JustSomeNewbie Aug 06 '16 at 20:08
0
declare @STORENUM Varchar(100) , @xml xml

set @STORENUM = '1,2'
set @xml = N'<root><r>' + replace(@STORENUM ,',','</r><r>') + '</r></root>'


SELECT DISTINCT 
    STORE_NUM, 
    ISNULL(LINE_1_ADDR, '') AS LINE_1_ADDR, 
    ISNULL(LINE_2_ADDR, '') AS LINE_2_ADDR, 
    ISNULL(LINE_3_ADDR, '') AS LINE_3_ADDR,
    CITY_NM, STATE_CD 
FROM 
    Store 
WHERE 
    STORE_NUM IN (
                  select r.value('.','varchar(max)') numbers 
                  from @xml.nodes('//root/r') as records(r)
                  )
M.Ali
  • 62,698
  • 12
  • 85
  • 116