Using list of values for a parameter as CTE/JOIN values

CSmith · December 31, 2024, 6:17pm

The approx SQL produced (I had to try this it’s very ingenious!) related to the OP:

/*
 * Disclaimer!!!
 * This is not a real query being executed, but a simplified version for general vision.
 * Executing it with any other tool may produce a different result.
 */
 
select 
	([Ice].entry(ListRowGen.Calculated_RowNum, @ListOfParams , ',')) as [Calculated_ParamsList],
	[Part].[Company] as [Part_Company],
	[Part].[PartNum] as [Part_PartNum]
from  (select top (20)  
	[PartTran].[Company] as [PartTran_Company],
	(ROW_NUMBER() OVER(ORDER BY PartTran.TranNum)) as [Calculated_RowNum]
from Erp.PartTran as PartTran)  as ListRowGen
inner join Erp.Part as Part on 
	Part.Company = ListRowGen.PartTran_Company
	and ( Part.PartNum like '%'+[Ice].entry(ListRowGen.Calculated_RowNum, @ListOfParams , ',')+'%'  )

Very cool!