SQL Conversion Issue

I have a temp table where i am creating all the values of column to a comma separated list.. the issue i am facing is when i am inputting that csv variable i am getting error For ex

Declare @testvar varchar (max)
Select  @testvar = stuff (( select (','''+ cast(var1 as varchar(1024)+'''')
From # Temp for xmlpath('')),1,2,'') from #Temp

Select * from tab1 where col1 in (@testvar)

This is appending single quotes in query i am getting error like Conversion failed while converting varchar value ''1','2'........ to int

Any suggestions for this

2 answers

  • answered 2018-11-08 08:39 D-Shih

    You seem to like doing dynamic SQL. make sure your brackets are all corresponded and correctly.

    set @testvar= stuff( 
    (select ',''' + cast(var1 as varchar(1024))+ ''''
    From #Temp for xml path(''))
    ,1,1,'') 
    
    
    Select * from tab1 where col1 in (@testvar)
    

    sqlfiddle

    Here is a sample

    CREATE TABLE #Temp(
        var1 INT
    );
    
    INSERT INTO #Temp VALUES (1);
    INSERT INTO #Temp VALUES (2);
    
    create table tab1(
      col1 int
    );
    
    insert into tab1 values (1)
    insert into tab1 values (3)
    
    Declare @testvar varchar (max)
    Declare @query varchar (max)
    
    set @testvar= stuff( 
    (select ',''' + cast(var1 as varchar(1024))+ ''''
    From #Temp for xml path(''))
    ,1,1,'') 
    
    
    SET @query = 'Select * from tab1 where col1 in ('+@testvar+')'
    
    
    
    execute(@QUERY)
    

  • answered 2018-11-08 09:01 Zohar Peled

    The IN operator expects a list of values. You are supplying it with a single value that happens to contain a comma delimited text. There's a difference that seems very subtle as a human, but to SQL Server it's a huge difference.

    A query like

    SELECT * FROM Tab1 WHERE Col1 IN ('A', 'B', 'C')
    

    will return all rows where col1 has the value A, B or C, while a query like

    SELECT * FROM Tab1 WHERE Col1 IN ('''A'', ''B'', ''C''')
    

    will return only the records where col1 has the value 'A', 'B', 'C' - so basically it's like writing

    SELECT * FROM Tab1 WHERE Col1 = '''A'', ''B'', ''C'''
    

    What you should do is using the temporary table directly:

    SELECT *
    FROM Tab1 
    WHERE EXISTS
    (
        SELECT 1
        FROM #Temp as tmp
        WHERE tmp.Var1 = Tab1.Col1
    )