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

0
votes

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))
3
sql-server
Error info is pretty clear u'r trying to bind varchar to int. U had to conver it first. - JustSomeNewbie
I tried it --> (cast(@STORENUM as int)) - user1155576
But you cant convert value with "," to int. How it should be done then? - JustSomeNewbie
I need to get the store details from Store table. For this i'm passing comma separated storelist from my C# code to this query. Is there a way to restrict the single quotes instead ?? If I pass 1,2 instead of '1,2' I'm getting the expected result.. - user1155576

3 Answers

1
votes

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)) + ',%'
0
votes

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)

0
votes
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)
                  )