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 = ''
@AllIDs = (@AllIDs + cast(ID as varchar(10)) + ',')
from EmployeeDetail WHERE Code In(
select * from TBL_OT where EmployeeId In(@AllIDs)
Conversion failed when converting the varchar value 'xxx' to data type
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.