CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2014
Amateur.techie
Amateur.techie

Reputation: 51

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

Upvotes: 0

Views: 75

Answers (2)

Zohar Peled
Zohar Peled

Reputation: 82524

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
)

Upvotes: 2

D-Shih
D-Shih

Reputation: 46249

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)

Upvotes: 1

Related Questions