CODEWITHSUNDEEP
sqlt-sqlssms
Terrance Jackson
Terrance Jackson

Reputation: 1079

Multiple integer rows into delimited comma for In Clause

I am able to get the values of loanids which are rows of integers into a comma delimited list. However I am unable to convert this list to be used for an In Clause

Declare @LoanNums AS NVARCHAR(Max) = ''
select
   DISTINCT
   @LoanNums =  
    stuff((
        select ',' + CONVERT(NVARCHAR(max), u.LoanId)
        from #tmpLoan u
        where u.LoanId = u.LoanId
        order by u.LoanId
        for xml path('')
    ),1,1,'')
from #tmpLoan

This now outputs 132254986,132255002,132255018,249736374,249892143 which looks fine however when I put this variable into an IN clause

DECLARE @SQL AS NVARCHAR(MAX) = ''
SELECT @SQL = 'Select * from myOtherTable Where LoanId in (''' + @LoanNums + ''')'

if outputs this SQL

Select * from myOtherTable WHERE LoanId in
 ('132254986,132255002,132255018,249736374,249892143')

and I get the error 

Conversion failed when converting the varchar value  
'132254986,132255002,132255018,249736374,249892143' to data type int

How can I create a proper formatted comma delimited list to be used correctly in an IN Clause?

Upvotes: 0

Views: 24

Answers (1)

Derviş Kayımbaşıoğlu
Derviş Kayımbaşıoğlu

Reputation: 30663

isn't Select * from myOtherTable WHERE LoanId in ('132254986,132255002,132255018,249736374,249892143') needed to be

Select * from myOtherTable WHERE LoanId in
  ('132254986','132255002','132255018','249736374','249892143');
             ^ ^         ^ ^         ^ ^         ^ ^

Upvotes: 1

Related Questions