By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
declare @StoreID int = 0
declare @OrderIndex int = 0
declare @availabilityStatus varchar(max) = '1,2'
declare @sql nvarchar(max)
set @sql =
'SELECT Distinct P.ProductId, P.BrandId, P.[' + cast(@OrderIndex as varchar(100)) + '] FROM Products P INNER JOIN ProductStores PS ON P.ProductId = PS.ProductId ' +
'Where PS.StoreId = '+ cast(@StoreId as varchar(100))+ ' and (PS.Status IN (' + @availabilityStatus + ') or (PS.StoreId != '+cast(@StoreId as varchar(100))+' and PS.Status IN(0,2))'
Print @sql
SELECT Distinct P.ProductId, P.BrandId, P.[0] FROM Products P INNER JOIN ProductStores PS ON P.ProductId = PS.ProductId Where PS.StoreId = 0 and (PS.Status IN (1,2) or (PS.StoreId != 0 and PS.Status IN(0,2))