Xtykutl

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

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)

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

)