I have next tables: Sales, Products, Brands
I want to display all sales for products belonging to a list of brands (Ids) ',123,2354,4567,' OR ALL Brands (if this list is empty)

Using the function developed by CVMichael and me, dbo.fn_SplitStringToTable http://www.vbforums.com/showthread.php?t=590692

My stored procedure receives the list of brands as:

@BrandId_List = ',2,3,5,6,' -- for all brands the list would be empty

Code:
DECLARE @tbl_Brands TABLE (BrandID INT)
	
	INSERT INTO @tbl_Brands
	SELECT ItemData 
	FROM dbo.fn_SplitStringToTable (ISNULL(@BrandId_List,''),',') your_Brands
I can do it like this:

Code:
SELECT *
FROM 
	Sales s
		INNER JOIN	Products p on c.customerId = p.customerId
		INNER JOIN	Brands b on p.BrandId = b.BrandId -- do the LEFT JOIN Brands if you have products without brands
		LEFT JOIN	@tbl_Brands tb on b.brandId = tb.BrandId
WHERE
	@BrandId_List = '' 
	OR 
	tb.BrandId is NOT NULL
--OR I can do it like this (the way I RECOMMEND ):

Code:
SELECT *
FROM 
	Sales s
		INNER JOIN	Products p on c.customerId = p.customerId
		LEFT JOIN	Brands b 
			INNER JOIN	@tbl_Brands tb on b.brandId = tb.BrandId
		on p.BrandId = b.BrandId
WHERE
	@BrandId_List = '' 
	OR 
	b.BrandId is NOT NULL
	
/* You can use this technique instead of:

...	INNER JOIN	Products p on c.customerId = p.customerId
		LEFT JOIN (SELECT br.* FROM Brands br INNER JOIN	@tbl_Brands tb on br.brandId = tb.BrandId) b
	on p.BrandId = b.BrandId
*/
Enjoy!