SET LANGUAGE Dutch; create table #catTable ( CategoryID int ) ;WITH cte AS ( SELECT a.CategoryID FROM Category a WHERE CategoryID = @CategoryID OR (@CategoryID=0) UNION ALL SELECT a.CategoryID FROM Category a JOIN cte c ON a.fkCategoryId_Mother = c.CategoryID ) INSERT INTO #catTable ( CategoryID ) SELECT * FROM cte ;WITH PagedProducts AS ( SELECT DISTINCT ProductID as pId, ROW_NUMBER() OVER(ORDER BY Weight DESC, ((Clicks+1) - ((Clicks+1) % 200)) DESC, ProductId desc ) AS RowNumber FROM ( select DISTINCT ProductID, Clicks, FORMAT(SalePrice, 'N', 'nl-NL') as SalePrice, FORMAT(Price, 'N', 'nl-NL') as Price, Price as PriceOriginal, SalePrice as SalePriceOriginal, Weight FROM Product INNER JOIN ProductCategory ON ProductID = ProductCategory.fkProductID INNER JOIN Webshop as WShop ON WShop.WebshopID = Product.fkWebshopID INNER JOIN #catTable ON fkCategoryID = CategoryID INNER JOIN ProductProperty as Pp ON Pp.fkPropertyID=9 AND Pp.fkProductID=ProductID WHERE Product.[Online] = 1 AND isNull(Product.URL,'') <> '' AND isNull(Product.fkProductStatusId,0) NOT in (3,4,5) ) vw ) SELECT * FROM ( SELECT DISTINCT (Clicks+1) - ((Clicks+1) % 40) as groupedClicks, RowNumber, Nrf.[URL], Case when ImageUrl like '/uploads/%' THEN '/products/p-' + convert(varchar(50), productid) + '.' + (case when imageurl like '%.%' and charindex('.', reverse(imageurl)) < 6 then reverse(left(reverse(imageurl), charindex('.', reverse(imageurl)) - 1)) else 'jpg' end) ELSE Replace(isNull(ImageURL,''), 'http://', '//') END as ImageUrl, Case when ImageUrl like '/uploads/%' THEN '/products/p-' + convert(varchar(50), productid) + '-w255.' + (case when imageurl like '%.%' and charindex('.', reverse(imageurl)) < 6 then reverse(left(reverse(imageurl), charindex('.', reverse(imageurl)) - 1)) else 'jpg' end) ELSE Replace(isNull(ImageURL,''), 'http://', '//') END as ImageUrl255, Case when ImageUrl like '/uploads/%' THEN '/products/p-' + convert(varchar(50), productid) + '-w540.' + (case when imageurl like '%.%' and charindex('.', reverse(imageurl)) < 6 then reverse(left(reverse(imageurl), charindex('.', reverse(imageurl)) - 1)) else 'jpg' end) ELSE Replace(isNull(ImageURL,''), 'http://', '//') END as ImageUrl540 , ProductID, Title, CASE WHEN LEN([Title]) >= 15 THEN LEFT([Title], 15) + '...' ELSE [Title] END AS Bait, FORMAT(Price, 'N', 'nl-NL') as Price, FORMAT(SalePrice, 'N', 'nl-NL') as SalePrice, Price as PriceOriginal, SalePrice as SalePriceOriginal, CASE WHEN isNull(SalePrice,0) = Price OR isNull(SalePrice,0)=0 THEN '' else FORMAT(SalePrice, 'N', 'nl-NL') END as SalePriceString, dbo.makeURL(Title) as href, Pp.Value as Brand, @PageSize as ps, @PageNum as pn, --CASE WHEN LEN(Replace(Description,'

','')) >= 20 THEN LEFT(Replace(Description,'

',''), 20) + '...' ELSE Replace(Description,'

','') END AS PreDesc, CASE WHEN LEN(WShop.[name]) >= 15 THEN LEFT(WShop.[name], 15) + '...' ELSE WShop.[Name] END AS ButtonText, Clicks, Weight FROM PagedProducts INNER JOIN Product AS Nrf ON pId = ProductID INNER JOIN ProductCategory ON ProductID = ProductCategory.fkProductID INNER JOIN Webshop as WShop ON WShop.WebshopID = Nrf.fkWebshopID INNER JOIN #catTable ON fkCategoryID = CategoryID INNER JOIN ProductProperty as Pp ON Pp.fkPropertyID=9 AND Pp.fkProductID=ProductID WHERE (Nrf.[Online] = 1 AND isNull(Nrf.[URL],'') <> '' AND isNull(Nrf.fkProductStatusId,0) NOT in (3,4,5)) AND ( RowNumber > (@PageNum-1) * @PageSize ) AND ( RowNumber <= (@PageNum * @PageSize) ) ) as tblAll ORDER BY Weight DESC, ((Clicks+1) - ((Clicks+1) % 200)) DESC, ProductId desc SELECT count(*) FROM ( select DISTINCT ProductId FROM Product INNER JOIN ProductCategory ON ProductID = ProductCategory.fkProductID INNER JOIN Webshop as WShop ON WShop.WebshopID = Product.fkWebshopID INNER JOIN #catTable ON fkCategoryID = CategoryID INNER JOIN ProductProperty as Pp ON Pp.fkPropertyID=9 AND Pp.fkProductID=ProductID WHERE (Product.[Online] = 1 AND isNull(Product.[URL],'') <> '' AND isNull(Product.fkProductStatusId,0) NOT in (3,4,5)) ) as tblAll SELECT DISTINCT PropertyID, Property.Name FROM ProductProperty INNER JOIN Property ON PropertyID = fkPropertyID WHERE ShowAsFilter = 1 ; SELECT DISTINCT PropertyID, replace(ProductProperty.Value, '%', '') as PropertyValue, dbo.UrlEncode(isNull(ProductProperty.Value,'')) as PropertyValueSafe FROM ProductProperty INNER JOIN Property ON PropertyID = fkPropertyID WHERE ShowAsFilter = 1 AND NOT ProductProperty.Value is null AND NOT ProductProperty.Value = 'null' AND fkProductID in ( SELECT ProductID FROM Product INNER JOIN ProductCategory ON ProductID = ProductCategory.fkProductID INNER JOIN Webshop as WShop ON WShop.WebshopID = Product.fkWebshopID INNER JOIN #catTable ON fkCategoryID = CategoryID WHERE Product.[Online] = 1 AND isNull(Product.[URL],'') <> '' ) ORDER BY PropertyValue SELECT ProductTag.fkTagId into #tmptags FROM Product INNER JOIN ProductCategory ON Product.ProductID = ProductCategory.fkProductID INNER JOIN #catTable ON fkCategoryID = CategoryID INNER JOIN [ProductTag] ON [ProductTag].fkProductID = ProductID WHERE Product.[Online] = 1 AND isNull(Product.[URL],'') <> '' SELECT DISTINCT TagId as PropertyID, tag.Name as PropertyValue, dbo.UrlEncode(isNull(tag.Name,'')) as PropertyValueSafe, tag.Description as PropertyDescription, OrderNum FROM Tag WHERE fkTagTypeId=1 and online=1 AND exists ( SELECT TagId FROM #tmptags where fkTagID =tagid ) ORDER BY OrderNum SELECT [WebshopID] as PropertyID ,[Name] as PropertyValue, dbo.UrlEncode(isNull(Name,'')) as PropertyValueSafe FROM [dbo].[Webshop] WHERE Webshopid in ( SELECT fkWebshopId FROM Product INNER JOIN ProductCategory ON ProductID = ProductCategory.fkProductID INNER JOIN #catTable ON fkCategoryID = CategoryID WHERE Product.[Online] = 1 AND isNull(Product.[URL],'') <> '' ) ORDER BY Name DROP TABLE #tmptags DROP TABLE #catTable; ;

Visvervanger