Getting error when putting VARCHAR data and searching INT value

Question

Below is my query and error. When I am putting VARCHAR variable and pass in INT value then I am getting error.

Query:

    declare @vid VARCHAR(MAX)
    set @vid= ('3,5,7')

    Select (Stuff((Select ', ' + VehicleClassName 
    From VehicleClass_Master  
    where VehicleClassId  in (@VID) FOR XML PATH('')),1,2,''))

Error:

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

Possible duplicate https://stackoverflow.com/q/337704/1048425 — GarethD, Sep 26 '17 at 07:44

score 1 · Answer 1 · answered Sep 26 '17 at 07:31

Your variable @vid is a varchar containing non-numeric characters (,); also it is not a "list" it is simply a string containing a single value that happens to have numbers and commas in it.

To acheive what you want, create @vid as a table variable with a single INT column, then insert each value as a separate row - INSERT @vid_table(vid) VALUES (3),(5),(7);

Finally change your IN (@VID) to IN (SELECT VID FROM @vid_table)

score 0 · Answer 2 · edited Sep 26 '17 at 07:42

0

declare @vid VARCHAR(MAX),@query varchar(max)
set @vid= ('3,5,7')
SET @query = 'Select (Stuff((Select '', '' + VehicleClassName From VehicleClass_Master  where VehicleClassId  in (' + @vid + ') FOR XML PATH('''')),1,2,''''))'
EXEC (@query)

edited Sep 26 '17 at 07:42

Ilyes

14,640
4
29
55

answered Sep 26 '17 at 07:37

Rushang

1
3

Or you could do it as dynamic SQL :) – SQLBadPanda Sep 26 '17 at 07:42

Getting error when putting VARCHAR data and searching INT value

2 Answers2