SQL Server - JOIN technique

[CVMichael]

You don't need to split, just convert your ID to string, then search that string in the list.

Like this:

DECLARE @BrandId_List VARCHAR(MAX)
SET @BrandId_List = ',123,2354,4567,'

SELECT *
FROM Sales s
	INNER JOIN	Products p on c.customerId = p.customerId
	INNER JOIN	Brands b on p.BrandId = b.BrandId
WHERE CHARINDEX(',' + CAST(b.brandId AS VARCHAR(10)) + ',', @BrandId_List) > 0
	OR NULLIF(@BrandId_List, '') IS NULL