Anup Anup - 2 days ago 4
SQL Question

Conversion failed when converting the varchar value 'xxx' to data type int

I have declared a variable & appended strings with comma separated. This variable i want to use for a where condition for another query.

declare @AllIDs varchar(max)
set @AllIDs = ''

select
@AllIDs = (@AllIDs + cast(ID as varchar(10)) + ',')
from EmployeeDetail WHERE Code In(
'ABC1','ABC2'
)

select @AllIDs

select * from TBL_OT where EmployeeId In(@AllIDs)


The EmployeeId is of type int. so it is giving me error :-


Conversion failed when converting the varchar value 'xxx' to data type
int.

Answer

you need to use DynamicSQL:

declare @AllIDs nvarchar(max)
set @AllIDs = '('

select 
    @AllIDs = (@AllIDs + cast(ID as nvarchar(10)) + ',') 
from EmployeeDetail WHERE Code like 'ABC%'

set @AllIDs = @AllIDs + '-555)'

declare @sql nvarchar(max) = ' select * from Tbl_OT where EmployeeId In ' + @ALLIDs + ' AND OTDate = '2016-11-06''

print @sql
exec sp_executesql @sql,N''

The -555 as the last ID in the list is there to add to the last comma which otherwise will break syntax. Use some non-existing ID there.

Comments