If in CTE Stored Procedure MS SQL 2008

Hello All,

I'm trying to build a stored procedure that queries the database and returns paginated results, I'm doing this using a CTE.

But where it gets difficult is the query contains 3 if statements, how do I get this to work within the CTE?

Here is my code so far --

CREATE PROCEDURE [dbo].[PriceGuideSearch]
(
@market int,
@brand int,
@partner int,
@showall char(1),
@search nvarchar(500),
@cat1 nvarchar(50),
@cat2 nvarchar(50),
@cat3 nvarchar(50)
)
AS
BEGIN
SET NOCOUNT ON;
SELECT @search = '%' + LTRIM(@search);
SELECT @search = RTRIM(@search) + '%';

      IF @offset is null set @offset = 0
      set @offset = @offset +1
      If @pagecount is null set @pagecount = 10
      Declare @lastrecord int
      Set @lastrecord = @offset + @pagecount -1
      BEGIN
      -- THE ROW_NUMBER CAN BE USED IN COMMON TABEL EXPRESSION
            ;WITH PGCTE
            AS(
            BEGIN
If @brand IS NOT NULL
BEGIN
	SELECT 
		PG.ID, 
		Brand, 
		Replace(B.Name,'|', ',') Name,
		Replace(ProductCode,'|', ',') ProductCode, 
		Replace(Category1,'|', ',') Category1, 
		Replace(Category2,'|', ',') Category2, 
		Replace(Category3,'|', ',') Category3, 
		Replace(Category4,'|', ',') Category4, 
		Replace(Desc1,'|', ',') Desc1, 
		Trade, 
		TGI, 
		SSP,
        ROW_NUMBER() OVER (ORDER BY [ID] desc) AS RowNum 
		FROM dbo.PriceGuide PG inner join dbo.Brand B 
		on PG.Brand = B.ID 
		WHERE Market = @market
		AND Brand = @brand
		AND (Category1 like '%'+ @cat1 + '%' OR @cat1 IS NULL)
		AND (Category2 like '%'+ @cat2 + '%' OR @cat2 IS NULL)
		AND (Category3 like '%'+ @cat3 + '%' OR @cat3 IS NULL)
		AND ((B.Name LIKE @search OR @search IS NULL)
		OR (Category1 LIKE @search OR @search IS NULL) 
		OR (Category2 LIKE @search OR @search IS NULL)
		OR (Category3 LIKE @search OR @search IS NULL)
		OR (ProductCode LIKE @search OR @search IS NULL)
		OR (Desc1 LIKE @search OR @search IS NULL)
		OR (Desc2 LIKE @search OR @search IS NULL)
		OR (PG.Desc3 LIKE @search OR @search IS NULL)
		OR (PG.Desc4 LIKE @search OR @search IS NULL)
		OR (PG.Colour LIKE @search OR @search IS NULL))		
		Order by  B.Name asc, Category1 asc, Category2 asc, Category3 asc, Category4 asc
END
If @brand IS NULL AND @showall = 'Y'
BEGIN
		SELECT 
		PG.ID, 
		PG.Brand, 
		Replace(B.Name,'|', ',') Name,
		Replace(ProductCode,'|', ',') ProductCode, 
		Replace(Category1,'|', ',') Category1, 
		Replace(Category2,'|', ',') Category2, 
		Replace(Category3,'|', ',') Category3, 
		Replace(Category4,'|', ',') Category4, 
		Replace(Desc1,'|', ',') Desc1, 
		Trade, 
		TGI, 
		SSP,
        ROW_NUMBER() OVER (ORDER BY [ID] desc) AS RowNum  
		FROM dbo.PriceGuide PG inner join dbo.Brand B 
		on PG.Brand = B.ID 
		WHERE Market = @market
		AND (Category1 like '%'+ @cat1 + '%' OR @cat1 IS NULL)
		AND (Category2 like '%'+ @cat2 + '%' OR @cat2 IS NULL)
		AND (Category3 like '%'+ @cat3 + '%' OR @cat3 IS NULL)
		AND ((B.Name LIKE @search OR @search IS NULL)
		OR (Category1 LIKE @search OR @search IS NULL) 
		OR (Category2 LIKE @search OR @search IS NULL)
		OR (Category3 LIKE @search OR @search IS NULL)
		OR (ProductCode LIKE @search OR @search IS NULL)
		OR (Desc1 LIKE @search OR @search IS NULL)
		OR (Desc2 LIKE @search OR @search IS NULL)
		OR (PG.Desc3 LIKE @search OR @search IS NULL)
		OR (PG.Desc4 LIKE @search OR @search IS NULL)
		OR (PG.Colour LIKE @search OR @search IS NULL))
END
If @brand IS NULL AND @showall <> 'Y'
BEGIN
		SELECT 
		PG.ID, 
		PG.Brand, 
		Replace(B.Name,'|', ',') Name,
		Replace(ProductCode,'|', ',') ProductCode, 
		Replace(Category1,'|', ',') Category1, 
		Replace(Category2,'|', ',') Category2, 
		Replace(Category3,'|', ',') Category3, 
		Replace(Category4,'|', ',') Category4, 
		Replace(Desc1,'|', ',') Desc1, 
		Trade, 
		TGI, 
		SSP,
        ROW_NUMBER() OVER (ORDER BY [ID] desc) AS RowNum  
		FROM dbo.PriceGuide PG inner join dbo.Brand B 
		on PG.Brand = B.ID 
		WHERE Market = @market
		AND Brand in (Select Brand from dbo.PriceGuidePartnerSuppliers WHERE PartnerID = @partner)
		AND (Category1 like '%'+ @cat1 + '%' OR @cat1 IS NULL)
		AND (Category2 like '%'+ @cat2 + '%' OR @cat2 IS NULL)
		AND (Category3 like '%'+ @cat3 + '%' OR @cat3 IS NULL)
		AND ((B.Name LIKE @search OR @search IS NULL)
		OR (Category1 LIKE @search OR @search IS NULL) 
		OR (Category2 LIKE @search OR @search IS NULL)
		OR (Category3 LIKE @search OR @search IS NULL)
		OR (ProductCode LIKE @search OR @search IS NULL)
		OR (Desc1 LIKE @search OR @search IS NULL)
		OR (Desc2 LIKE @search OR @search IS NULL)
		OR (PG.Desc3 LIKE @search OR @search IS NULL)
		OR (PG.Desc4 LIKE @search OR @search IS NULL)
		OR (PG.Colour LIKE @search OR @search IS NULL))
		)
		Select
		ID, 
		Brand, 
		Name,
		ProductCode, 
		Category1, 
		Category2, 
		Category3, 
		Category4, 
		Desc1, 
		Trade, 
		TGI, 
		SSP,
        RowNum 
		FROM	PGCTE 
        WHERE   RowNum between @offset and @lastrecord
		Order by  Name asc, Category1 asc, Category2 asc, Category3 asc, Category4 asc
END	
END		
END

GO

Open in new window


Any assistance really appreciated - thanks
garethtnashAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Koen Van WielinkBusiness Intelligence SpecialistCommented:
Looks to me like you're missing some ELSE statements:
CREATE PROCEDURE [dbo].[PriceGuideSearch]
(
@market int,
@brand int,
@partner int,
@showall char(1),
@search nvarchar(500),
@cat1 nvarchar(50),
@cat2 nvarchar(50),
@cat3 nvarchar(50)
)
AS
BEGIN
SET NOCOUNT ON;
SELECT @search = '%' + LTRIM(@search);
SELECT @search = RTRIM(@search) + '%';

      IF @offset is null set @offset = 0
      set @offset = @offset +1
      If @pagecount is null set @pagecount = 10
      Declare @lastrecord int
      Set @lastrecord = @offset + @pagecount -1
      BEGIN
      -- THE ROW_NUMBER CAN BE USED IN COMMON TABEL EXPRESSION
            ;WITH PGCTE
            AS(
            BEGIN
If @brand IS NOT NULL
BEGIN
	SELECT 
		PG.ID, 
		Brand, 
		Replace(B.Name,'|', ',') Name,
		Replace(ProductCode,'|', ',') ProductCode, 
		Replace(Category1,'|', ',') Category1, 
		Replace(Category2,'|', ',') Category2, 
		Replace(Category3,'|', ',') Category3, 
		Replace(Category4,'|', ',') Category4, 
		Replace(Desc1,'|', ',') Desc1, 
		Trade, 
		TGI, 
		SSP,
        ROW_NUMBER() OVER (ORDER BY [ID] desc) AS RowNum 
		FROM dbo.PriceGuide PG inner join dbo.Brand B 
		on PG.Brand = B.ID 
		WHERE Market = @market
		AND Brand = @brand
		AND (Category1 like '%'+ @cat1 + '%' OR @cat1 IS NULL)
		AND (Category2 like '%'+ @cat2 + '%' OR @cat2 IS NULL)
		AND (Category3 like '%'+ @cat3 + '%' OR @cat3 IS NULL)
		AND ((B.Name LIKE @search OR @search IS NULL)
		OR (Category1 LIKE @search OR @search IS NULL) 
		OR (Category2 LIKE @search OR @search IS NULL)
		OR (Category3 LIKE @search OR @search IS NULL)
		OR (ProductCode LIKE @search OR @search IS NULL)
		OR (Desc1 LIKE @search OR @search IS NULL)
		OR (Desc2 LIKE @search OR @search IS NULL)
		OR (PG.Desc3 LIKE @search OR @search IS NULL)
		OR (PG.Desc4 LIKE @search OR @search IS NULL)
		OR (PG.Colour LIKE @search OR @search IS NULL))		
		Order by  B.Name asc, Category1 asc, Category2 asc, Category3 asc, Category4 asc
END
ELSE
If @brand IS NULL AND @showall = 'Y'
BEGIN
		SELECT 
		PG.ID, 
		PG.Brand, 
		Replace(B.Name,'|', ',') Name,
		Replace(ProductCode,'|', ',') ProductCode, 
		Replace(Category1,'|', ',') Category1, 
		Replace(Category2,'|', ',') Category2, 
		Replace(Category3,'|', ',') Category3, 
		Replace(Category4,'|', ',') Category4, 
		Replace(Desc1,'|', ',') Desc1, 
		Trade, 
		TGI, 
		SSP,
        ROW_NUMBER() OVER (ORDER BY [ID] desc) AS RowNum  
		FROM dbo.PriceGuide PG inner join dbo.Brand B 
		on PG.Brand = B.ID 
		WHERE Market = @market
		AND (Category1 like '%'+ @cat1 + '%' OR @cat1 IS NULL)
		AND (Category2 like '%'+ @cat2 + '%' OR @cat2 IS NULL)
		AND (Category3 like '%'+ @cat3 + '%' OR @cat3 IS NULL)
		AND ((B.Name LIKE @search OR @search IS NULL)
		OR (Category1 LIKE @search OR @search IS NULL) 
		OR (Category2 LIKE @search OR @search IS NULL)
		OR (Category3 LIKE @search OR @search IS NULL)
		OR (ProductCode LIKE @search OR @search IS NULL)
		OR (Desc1 LIKE @search OR @search IS NULL)
		OR (Desc2 LIKE @search OR @search IS NULL)
		OR (PG.Desc3 LIKE @search OR @search IS NULL)
		OR (PG.Desc4 LIKE @search OR @search IS NULL)
		OR (PG.Colour LIKE @search OR @search IS NULL))
END
ELSE
If @brand IS NULL AND @showall <> 'Y'
BEGIN
		SELECT 
		PG.ID, 
		PG.Brand, 
		Replace(B.Name,'|', ',') Name,
		Replace(ProductCode,'|', ',') ProductCode, 
		Replace(Category1,'|', ',') Category1, 
		Replace(Category2,'|', ',') Category2, 
		Replace(Category3,'|', ',') Category3, 
		Replace(Category4,'|', ',') Category4, 
		Replace(Desc1,'|', ',') Desc1, 
		Trade, 
		TGI, 
		SSP,
        ROW_NUMBER() OVER (ORDER BY [ID] desc) AS RowNum  
		FROM dbo.PriceGuide PG inner join dbo.Brand B 
		on PG.Brand = B.ID 
		WHERE Market = @market
		AND Brand in (Select Brand from dbo.PriceGuidePartnerSuppliers WHERE PartnerID = @partner)
		AND (Category1 like '%'+ @cat1 + '%' OR @cat1 IS NULL)
		AND (Category2 like '%'+ @cat2 + '%' OR @cat2 IS NULL)
		AND (Category3 like '%'+ @cat3 + '%' OR @cat3 IS NULL)
		AND ((B.Name LIKE @search OR @search IS NULL)
		OR (Category1 LIKE @search OR @search IS NULL) 
		OR (Category2 LIKE @search OR @search IS NULL)
		OR (Category3 LIKE @search OR @search IS NULL)
		OR (ProductCode LIKE @search OR @search IS NULL)
		OR (Desc1 LIKE @search OR @search IS NULL)
		OR (Desc2 LIKE @search OR @search IS NULL)
		OR (PG.Desc3 LIKE @search OR @search IS NULL)
		OR (PG.Desc4 LIKE @search OR @search IS NULL)
		OR (PG.Colour LIKE @search OR @search IS NULL))
		)
		Select
		ID, 
		Brand, 
		Name,
		ProductCode, 
		Category1, 
		Category2, 
		Category3, 
		Category4, 
		Desc1, 
		Trade, 
		TGI, 
		SSP,
        RowNum 
		FROM	PGCTE 
        WHERE   RowNum between @offset and @lastrecord
		Order by  Name asc, Category1 asc, Category2 asc, Category3 asc, Category4 asc
END	
END		
END

GO

Open in new window

0
garethtnashAuthor Commented:
Hi Kvwielink,

Thanks for that, I've just made a few changes, but the 'if' in '' ;WITH PGCTE
            AS(
If @brand IS NOT NULL
BEGIN''

is highlighted in red --

CREATE PROCEDURE [dbo].[PriceGuideSearch]
(
@market int,
@brand int,
@partner int,
@showall char(1),
@search nvarchar(500),
@cat1 nvarchar(50),
@cat2 nvarchar(50),
@cat3 nvarchar(50),
@offset int,
@pagecount int
)
AS
BEGIN
SET NOCOUNT ON;
SELECT @search = '%' + LTRIM(@search);
SELECT @search = RTRIM(@search) + '%';

      IF @offset is null set @offset = 0
      set @offset = @offset +1
      If @pagecount is null set @pagecount = 10
      Declare @lastrecord int
      Set @lastrecord = @offset + @pagecount -1
      BEGIN
      -- THE ROW_NUMBER CAN BE USED IN COMMON TABEL EXPRESSION
            ;WITH PGCTE
            AS(
If @brand IS NOT NULL
BEGIN
	SELECT 
		PG.ID, 
		Brand, 
		Replace(B.Name,'|', ',') Name,
		Replace(ProductCode,'|', ',') ProductCode, 
		Replace(Category1,'|', ',') Category1, 
		Replace(Category2,'|', ',') Category2, 
		Replace(Category3,'|', ',') Category3, 
		Replace(Category4,'|', ',') Category4, 
		Replace(Desc1,'|', ',') Desc1, 
		Trade, 
		TGI, 
		SSP,
        ROW_NUMBER() OVER (ORDER BY [ID] desc) AS RowNum 
		FROM dbo.PriceGuide PG inner join dbo.Brand B 
		on PG.Brand = B.ID 
		WHERE Market = @market
		AND Brand = @brand
		AND (Category1 like '%'+ @cat1 + '%' OR @cat1 IS NULL)
		AND (Category2 like '%'+ @cat2 + '%' OR @cat2 IS NULL)
		AND (Category3 like '%'+ @cat3 + '%' OR @cat3 IS NULL)
		AND ((B.Name LIKE @search OR @search IS NULL)
		OR (Category1 LIKE @search OR @search IS NULL) 
		OR (Category2 LIKE @search OR @search IS NULL)
		OR (Category3 LIKE @search OR @search IS NULL)
		OR (ProductCode LIKE @search OR @search IS NULL)
		OR (Desc1 LIKE @search OR @search IS NULL)
		OR (Desc2 LIKE @search OR @search IS NULL)
		OR (PG.Desc3 LIKE @search OR @search IS NULL)
		OR (PG.Desc4 LIKE @search OR @search IS NULL)
		OR (PG.Colour LIKE @search OR @search IS NULL))		
END
ELSE
If @brand IS NULL AND @showall = 'Y'
BEGIN
		SELECT 
		PG.ID, 
		PG.Brand, 
		Replace(B.Name,'|', ',') Name,
		Replace(ProductCode,'|', ',') ProductCode, 
		Replace(Category1,'|', ',') Category1, 
		Replace(Category2,'|', ',') Category2, 
		Replace(Category3,'|', ',') Category3, 
		Replace(Category4,'|', ',') Category4, 
		Replace(Desc1,'|', ',') Desc1, 
		Trade, 
		TGI, 
		SSP,
        ROW_NUMBER() OVER (ORDER BY [ID] desc) AS RowNum  
		FROM dbo.PriceGuide PG inner join dbo.Brand B 
		on PG.Brand = B.ID 
		WHERE Market = @market
		AND (Category1 like '%'+ @cat1 + '%' OR @cat1 IS NULL)
		AND (Category2 like '%'+ @cat2 + '%' OR @cat2 IS NULL)
		AND (Category3 like '%'+ @cat3 + '%' OR @cat3 IS NULL)
		AND ((B.Name LIKE @search OR @search IS NULL)
		OR (Category1 LIKE @search OR @search IS NULL) 
		OR (Category2 LIKE @search OR @search IS NULL)
		OR (Category3 LIKE @search OR @search IS NULL)
		OR (ProductCode LIKE @search OR @search IS NULL)
		OR (Desc1 LIKE @search OR @search IS NULL)
		OR (Desc2 LIKE @search OR @search IS NULL)
		OR (PG.Desc3 LIKE @search OR @search IS NULL)
		OR (PG.Desc4 LIKE @search OR @search IS NULL)
		OR (PG.Colour LIKE @search OR @search IS NULL))
END
ELSE
If @brand IS NULL AND @showall <> 'Y'
BEGIN
		SELECT 
		PG.ID, 
		PG.Brand, 
		Replace(B.Name,'|', ',') Name,
		Replace(ProductCode,'|', ',') ProductCode, 
		Replace(Category1,'|', ',') Category1, 
		Replace(Category2,'|', ',') Category2, 
		Replace(Category3,'|', ',') Category3, 
		Replace(Category4,'|', ',') Category4, 
		Replace(Desc1,'|', ',') Desc1, 
		Trade, 
		TGI, 
		SSP,
        ROW_NUMBER() OVER (ORDER BY [ID] desc) AS RowNum  
		FROM dbo.PriceGuide PG inner join dbo.Brand B 
		on PG.Brand = B.ID 
		WHERE Market = @market
		AND Brand in (Select Brand from dbo.PriceGuidePartnerSuppliers WHERE PartnerID = @partner)
		AND (Category1 like '%'+ @cat1 + '%' OR @cat1 IS NULL)
		AND (Category2 like '%'+ @cat2 + '%' OR @cat2 IS NULL)
		AND (Category3 like '%'+ @cat3 + '%' OR @cat3 IS NULL)
		AND ((B.Name LIKE @search OR @search IS NULL)
		OR (Category1 LIKE @search OR @search IS NULL) 
		OR (Category2 LIKE @search OR @search IS NULL)
		OR (Category3 LIKE @search OR @search IS NULL)
		OR (ProductCode LIKE @search OR @search IS NULL)
		OR (Desc1 LIKE @search OR @search IS NULL)
		OR (Desc2 LIKE @search OR @search IS NULL)
		OR (PG.Desc3 LIKE @search OR @search IS NULL)
		OR (PG.Desc4 LIKE @search OR @search IS NULL)
		OR (PG.Colour LIKE @search OR @search IS NULL))
END)
		Select
		ID, 
		Brand, 
		Name,
		ProductCode, 
		Category1, 
		Category2, 
		Category3, 
		Category4, 
		Desc1, 
		Trade, 
		TGI, 
		SSP,
        RowNum 
		FROM	PGCTE 
        WHERE   RowNum between @offset and @lastrecord
		Order by  Name asc, Category1 asc, Category2 asc, Category3 asc, Category4 asc
END	
END		
GO

Open in new window


as is the first 'ELSE' and all of --

)
            Select
            ID,
            Brand,
            Name,
            ProductCode,
            Category1,
            Category2,
            Category3,
            Category4,
            Desc1,
            Trade,
            TGI,
            SSP,
        RowNum
            FROM      PGCTE
        WHERE   RowNum between @offset and @lastrecord
            Order by  Name asc, Category1 asc, Category2 asc, Category3 asc, Category4 asc

Do CTE's not allow if statements within?

Thanks
0
garethtnashAuthor Commented:
Trying to build from a slightly different approach --

CREATE PROCEDURE [dbo].[PriceGuideSearch2]
(
@market int,
@brand int,
@partner int,
@showall char(1),
@search nvarchar(500),
@cat1 nvarchar(50),
@cat2 nvarchar(50),
@cat3 nvarchar(50),
@offset int,
@pagecount int
)
AS
BEGIN
SET NOCOUNT ON;
SELECT @search = '%' + LTRIM(@search);
SELECT @search = RTRIM(@search) + '%';
IF @offset is null set @offset = 0
set @offset = @offset +1
      If @pagecount is null set @pagecount = 10
      Declare @lastrecord int
      Set @lastrecord = @offset + @pagecount -1
BEGIN
If @brand IS NOT NULL
BEGIN
	;WITH JBCTE
	AS(
	SELECT 
	PG.ID, 
	Brand, 
	Replace(B.Name,'|', ',') Name,
	Replace(ProductCode,'|', ',') ProductCode, 
	Replace(Category1,'|', ',') Category1, 
	Replace(Category2,'|', ',') Category2, 
	Replace(Category3,'|', ',') Category3, 
	Replace(Category4,'|', ',') Category4, 
	Replace(Desc1,'|', ',') Desc1, 
	Trade, 
	TGI, 
	SSP,
	ROW_NUMBER() OVER (ORDER BY [ID] desc) AS RowNum
	FROM dbo.PriceGuide PG inner join dbo.Brand B 
	on PG.Brand = B.ID 
	WHERE Market = @market
	AND Brand = @brand
	AND (Category1 like '%'+ @cat1 + '%' OR @cat1 IS NULL)
	AND (Category2 like '%'+ @cat2 + '%' OR @cat2 IS NULL)
	AND (Category3 like '%'+ @cat3 + '%' OR @cat3 IS NULL)
	AND ((B.Name LIKE @search OR @search IS NULL)
	OR (Category1 LIKE @search OR @search IS NULL) 
	OR (Category2 LIKE @search OR @search IS NULL)
	OR (Category3 LIKE @search OR @search IS NULL)
	OR (ProductCode LIKE @search OR @search IS NULL)
	OR (Desc1 LIKE @search OR @search IS NULL)
	OR (Desc2 LIKE @search OR @search IS NULL)
	OR (PG.Desc3 LIKE @search OR @search IS NULL)
	OR (PG.Desc4 LIKE @search OR @search IS NULL)
	OR (PG.Colour LIKE @search OR @search IS NULL))	
	)
END
If @brand IS NULL AND @showall = 'Y'
BEGIN
	;WITH JBCTE
	AS(
	SELECT 
	PG.ID, 
	Brand, 
	Replace(B.Name,'|', ',') Name,
	Replace(ProductCode,'|', ',') ProductCode, 
	Replace(Category1,'|', ',') Category1, 
	Replace(Category2,'|', ',') Category2, 
	Replace(Category3,'|', ',') Category3, 
	Replace(Category4,'|', ',') Category4, 
	Replace(Desc1,'|', ',') Desc1, 
	Trade, 
	TGI, 
	SSP,
	ROW_NUMBER() OVER (ORDER BY [ID] desc) AS RowNum
	FROM dbo.PriceGuide PG inner join dbo.Brand B 
	on PG.Brand = B.ID 
	WHERE Market = @market
	AND (Category1 like '%'+ @cat1 + '%' OR @cat1 IS NULL)
	AND (Category2 like '%'+ @cat2 + '%' OR @cat2 IS NULL)
	AND (Category3 like '%'+ @cat3 + '%' OR @cat3 IS NULL)
	AND ((B.Name LIKE @search OR @search IS NULL)
	OR (Category1 LIKE @search OR @search IS NULL) 
	OR (Category2 LIKE @search OR @search IS NULL)
	OR (Category3 LIKE @search OR @search IS NULL)
	OR (ProductCode LIKE @search OR @search IS NULL)
	OR (Desc1 LIKE @search OR @search IS NULL)
	OR (Desc2 LIKE @search OR @search IS NULL)
	OR (PG.Desc3 LIKE @search OR @search IS NULL)
	OR (PG.Desc4 LIKE @search OR @search IS NULL)
	OR (PG.Colour LIKE @search OR @search IS NULL))
	)
END
If @brand IS NULL AND @showall <> 'Y'
BEGIN
	;WITH JBCTE
	AS(
	SELECT 
	PG.ID, 
	Brand, 
	Replace(B.Name,'|', ',') Name,
	Replace(ProductCode,'|', ',') ProductCode, 
	Replace(Category1,'|', ',') Category1, 
	Replace(Category2,'|', ',') Category2, 
	Replace(Category3,'|', ',') Category3, 
	Replace(Category4,'|', ',') Category4, 
	Replace(Desc1,'|', ',') Desc1, 
	Trade, 
	TGI, 
	SSP,
	ROW_NUMBER() OVER (ORDER BY [ID] desc) AS RowNum
	FROM dbo.PriceGuide PG inner join dbo.Brand B 
	on PG.Brand = B.ID 
	WHERE Market = @market
	AND Brand in (Select Brand from dbo.PriceGuidePartnerSuppliers WHERE PartnerID = @partner)
	AND (Category1 like '%'+ @cat1 + '%' OR @cat1 IS NULL)
	AND (Category2 like '%'+ @cat2 + '%' OR @cat2 IS NULL)
	AND (Category3 like '%'+ @cat3 + '%' OR @cat3 IS NULL)
	AND ((B.Name LIKE @search OR @search IS NULL)
	OR (Category1 LIKE @search OR @search IS NULL) 
	OR (Category2 LIKE @search OR @search IS NULL)
	OR (Category3 LIKE @search OR @search IS NULL)
	OR (ProductCode LIKE @search OR @search IS NULL)
	OR (Desc1 LIKE @search OR @search IS NULL)
	OR (Desc2 LIKE @search OR @search IS NULL)
	OR (PG.Desc3 LIKE @search OR @search IS NULL)
	OR (PG.Desc4 LIKE @search OR @search IS NULL)
	OR (PG.Colour LIKE @search OR @search IS NULL))
	)
END

SELECT	
ID, 
Brand, 
Name,
ProductCode, 
Category1, 
Category2, 
Category3, 
Category4, 
Desc1, 
Trade, 
TGI, 
SSP,
RowNum
FROM	JBCTE 
WHERE   RowNum between @offset and @lastrecord
Order by  Name asc, Category1 asc, Category2 asc, Category3 asc, Category4 asc

END
END

Open in new window


Still got red lines..

The three 'END' that create the JBECTE... and all of --

SELECT	
ID, 
Brand, 
Name,
ProductCode, 
Category1, 
Category2, 
Category3, 
Category4, 
Desc1, 
Trade, 
TGI, 
SSP,
RowNum
FROM	JBCTE 
WHERE   RowNum between @offset and @lastrecord
Order by  Name asc, Category1 asc, Category2 asc, Category3 asc, Category4 asc

Open in new window



Help !

Thanks

GTN
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

jogosCommented:
A CTE is not a procedure, so no IF .

You must do your branching in the query

           ;WITH PGCTE
            AS(
	SELECT 
		PG.ID, 
		Brand, 
		Replace(B.Name,'|', ',') Name,
		Replace(ProductCode,'|', ',') ProductCode, 
		Replace(Category1,'|', ',') Category1, 
		Replace(Category2,'|', ',') Category2, 
		Replace(Category3,'|', ',') Category3, 
		Replace(Category4,'|', ',') Category4, 
		Replace(Desc1,'|', ',') Desc1, 
		Trade, 
		TGI, 
		SSP,
        ROW_NUMBER() OVER (ORDER BY [ID] desc) AS RowNum 
		FROM dbo.PriceGuide PG inner join dbo.Brand B 
		on PG.Brand = B.ID 
		WHERE Market = @market

		AND (@brand IS NULL OR Brand = @brand)  -- If @brand IS NOT NULL

		AND (Category1 like '%'+ @cat1 + '%' OR @cat1 IS NULL)
		AND (Category2 like '%'+ @cat2 + '%' OR @cat2 IS NULL)
		AND (Category3 like '%'+ @cat3 + '%' OR @cat3 IS NULL)
		AND ((B.Name LIKE @search OR @search IS NULL)
		OR (Category1 LIKE @search OR @search IS NULL) 
		OR (Category2 LIKE @search OR @search IS NULL)
		OR (Category3 LIKE @search OR @search IS NULL)
		OR (ProductCode LIKE @search OR @search IS NULL)
		OR (Desc1 LIKE @search OR @search IS NULL)
		OR (Desc2 LIKE @search OR @search IS NULL)
		OR (PG.Desc3 LIKE @search OR @search IS NULL)
		OR (PG.Desc4 LIKE @search OR @search IS NULL)
		OR (PG.Colour LIKE @search OR @search IS NULL))		

Open in new window

0
garethtnashAuthor Commented:
Ok,...

So I cant use an IF within the CTE, what about before?

Thanks
0
Koen Van WielinkBusiness Intelligence SpecialistCommented:
You're right, might be a limitation to the use of a CTE.
Is there a reason it has to be a CTE? Can you instead use a temporary table?

CREATE PROCEDURE [dbo].[PriceGuideSearch]
(
@market int,
@brand int,
@partner int,
@showall char(1),
@search nvarchar(500),
@cat1 nvarchar(50),
@cat2 nvarchar(50),
@cat3 nvarchar(50),
@offset int,
@pagecount int
)
AS
BEGIN
SET NOCOUNT ON
SELECT @search = '%' + LTRIM(@search)
SELECT @search = RTRIM(@search) + '%'

      IF @offset is null set @offset = 0
      set @offset = @offset +1
      If @pagecount is null set @pagecount = 10
      Declare @lastrecord int
      Set @lastrecord = @offset + @pagecount -1
      BEGIN

--Declare temp table to hold the data
      
Create Table #RowNumber
(	ID			int
,	Brand		nvarchar(100)
,	Name		nvarchar(100)
,	ProductCode	nvarchar(100)
,	Category1	nvarchar(50)
,	Category2	nvarchar(50)
,	Category3	nvarchar(50)
,	Category4	nvarchar(50)
,	Desc1		nvarchar(100)
,	Trade		nvarchar(100)
,	TGI			nvarchar(100)
,	SSP			nvarchar(100)
,	RowNum		int
)

If @brand IS NOT NULL
BEGIN

INSERT INTO #RowNumber

	SELECT 
		PG.ID, 
		Brand, 
		Replace(B.Name,'|', ',') Name,
		Replace(ProductCode,'|', ',') ProductCode, 
		Replace(Category1,'|', ',') Category1, 
		Replace(Category2,'|', ',') Category2, 
		Replace(Category3,'|', ',') Category3, 
		Replace(Category4,'|', ',') Category4, 
		Replace(Desc1,'|', ',') Desc1, 
		Trade, 
		TGI, 
		SSP,
        ROW_NUMBER() OVER (ORDER BY [ID] desc) AS RowNum 
		FROM dbo.PriceGuide PG inner join dbo.Brand B 
		on PG.Brand = B.ID 
		WHERE Market = @market
		AND Brand = @brand
		AND (Category1 like '%'+ @cat1 + '%' OR @cat1 IS NULL)
		AND (Category2 like '%'+ @cat2 + '%' OR @cat2 IS NULL)
		AND (Category3 like '%'+ @cat3 + '%' OR @cat3 IS NULL)
		AND ((B.Name LIKE @search OR @search IS NULL)
		OR (Category1 LIKE @search OR @search IS NULL) 
		OR (Category2 LIKE @search OR @search IS NULL)
		OR (Category3 LIKE @search OR @search IS NULL)
		OR (ProductCode LIKE @search OR @search IS NULL)
		OR (Desc1 LIKE @search OR @search IS NULL)
		OR (Desc2 LIKE @search OR @search IS NULL)
		OR (PG.Desc3 LIKE @search OR @search IS NULL)
		OR (PG.Desc4 LIKE @search OR @search IS NULL)
		OR (PG.Colour LIKE @search OR @search IS NULL))		

END
ELSE
If @brand IS NULL AND @showall = 'Y'
BEGIN

INSERT INTO #RowNumber

		SELECT 
		PG.ID, 
		PG.Brand, 
		Replace(B.Name,'|', ',') Name,
		Replace(ProductCode,'|', ',') ProductCode, 
		Replace(Category1,'|', ',') Category1, 
		Replace(Category2,'|', ',') Category2, 
		Replace(Category3,'|', ',') Category3, 
		Replace(Category4,'|', ',') Category4, 
		Replace(Desc1,'|', ',') Desc1, 
		Trade, 
		TGI, 
		SSP,
        ROW_NUMBER() OVER (ORDER BY [ID] desc) AS RowNum  
		FROM dbo.PriceGuide PG inner join dbo.Brand B 
		on PG.Brand = B.ID 
		WHERE Market = @market
		AND (Category1 like '%'+ @cat1 + '%' OR @cat1 IS NULL)
		AND (Category2 like '%'+ @cat2 + '%' OR @cat2 IS NULL)
		AND (Category3 like '%'+ @cat3 + '%' OR @cat3 IS NULL)
		AND ((B.Name LIKE @search OR @search IS NULL)
		OR (Category1 LIKE @search OR @search IS NULL) 
		OR (Category2 LIKE @search OR @search IS NULL)
		OR (Category3 LIKE @search OR @search IS NULL)
		OR (ProductCode LIKE @search OR @search IS NULL)
		OR (Desc1 LIKE @search OR @search IS NULL)
		OR (Desc2 LIKE @search OR @search IS NULL)
		OR (PG.Desc3 LIKE @search OR @search IS NULL)
		OR (PG.Desc4 LIKE @search OR @search IS NULL)
		OR (PG.Colour LIKE @search OR @search IS NULL))

END
ELSE
If @brand IS NULL AND @showall <> 'Y'
BEGIN

INSERT INTO #RowNumber

		SELECT 
		PG.ID, 
		PG.Brand, 
		Replace(B.Name,'|', ',') Name,
		Replace(ProductCode,'|', ',') ProductCode, 
		Replace(Category1,'|', ',') Category1, 
		Replace(Category2,'|', ',') Category2, 
		Replace(Category3,'|', ',') Category3, 
		Replace(Category4,'|', ',') Category4, 
		Replace(Desc1,'|', ',') Desc1, 
		Trade, 
		TGI, 
		SSP,
        ROW_NUMBER() OVER (ORDER BY [ID] desc) AS RowNum  
		FROM dbo.PriceGuide PG inner join dbo.Brand B 
		on PG.Brand = B.ID 
		WHERE Market = @market
		AND Brand in (Select Brand from dbo.PriceGuidePartnerSuppliers WHERE PartnerID = @partner)
		AND (Category1 like '%'+ @cat1 + '%' OR @cat1 IS NULL)
		AND (Category2 like '%'+ @cat2 + '%' OR @cat2 IS NULL)
		AND (Category3 like '%'+ @cat3 + '%' OR @cat3 IS NULL)
		AND ((B.Name LIKE @search OR @search IS NULL)
		OR (Category1 LIKE @search OR @search IS NULL) 
		OR (Category2 LIKE @search OR @search IS NULL)
		OR (Category3 LIKE @search OR @search IS NULL)
		OR (ProductCode LIKE @search OR @search IS NULL)
		OR (Desc1 LIKE @search OR @search IS NULL)
		OR (Desc2 LIKE @search OR @search IS NULL)
		OR (PG.Desc3 LIKE @search OR @search IS NULL)
		OR (PG.Desc4 LIKE @search OR @search IS NULL)
		OR (PG.Colour LIKE @search OR @search IS NULL))

END
		Select
		ID, 
		Brand, 
		Name,
		ProductCode, 
		Category1, 
		Category2, 
		Category3, 
		Category4, 
		Desc1, 
		Trade, 
		TGI, 
		SSP,
        RowNum 
		FROM	#RowNumber
        WHERE   RowNum between @offset and @lastrecord
		Order by  Name asc, Category1 asc, Category2 asc, Category3 asc, Category4 asc
END	
END		

Drop Table #RowNumber

GO

Open in new window

0
Koen Van WielinkBusiness Intelligence SpecialistCommented:
Forgot to mention I made assumptions about the datatypes in the temp table creation. Please check and correct any mistakes in the datatypes.
0
garethtnashAuthor Commented:
Thanks Kvwielink,

#temporary tables, not sure, always done this historically using CTEs... what is the difference?

So I've changed my code slightly ....

CREATE PROCEDURE [dbo].[PriceGuideSearch2]
(
@market int,
@brand int,
@partner int,
@showall char(1),
@search nvarchar(500),
@cat1 nvarchar(50),
@cat2 nvarchar(50),
@cat3 nvarchar(50),
@offset int,
@pagecount int
)
AS
BEGIN
SET NOCOUNT ON;
SELECT @search = '%' + LTRIM(@search);
SELECT @search = RTRIM(@search) + '%';
IF @offset is null set @offset = 0
set @offset = @offset +1
      If @pagecount is null set @pagecount = 10
      Declare @lastrecord int
      Set @lastrecord = @offset + @pagecount -1
BEGIN
If @brand IS NOT NULL
BEGIN
	;WITH JBCTE
	AS(
	SELECT 
	PG.ID, 
	Brand, 
	Replace(B.Name,'|', ',') Name,
	Replace(ProductCode,'|', ',') ProductCode, 
	Replace(Category1,'|', ',') Category1, 
	Replace(Category2,'|', ',') Category2, 
	Replace(Category3,'|', ',') Category3, 
	Replace(Category4,'|', ',') Category4, 
	Replace(Desc1,'|', ',') Desc1, 
	Trade, 
	TGI, 
	SSP,
	ROW_NUMBER() OVER (ORDER BY [PG.ID] desc) AS RowNum
	FROM dbo.PriceGuide PG inner join dbo.Brand B 
	on PG.Brand = B.ID 
	WHERE Market = @market
	AND Brand = @brand
	AND (Category1 like '%'+ @cat1 + '%' OR @cat1 IS NULL)
	AND (Category2 like '%'+ @cat2 + '%' OR @cat2 IS NULL)
	AND (Category3 like '%'+ @cat3 + '%' OR @cat3 IS NULL)
	AND ((B.Name LIKE @search OR @search IS NULL)
	OR (Category1 LIKE @search OR @search IS NULL) 
	OR (Category2 LIKE @search OR @search IS NULL)
	OR (Category3 LIKE @search OR @search IS NULL)
	OR (ProductCode LIKE @search OR @search IS NULL)
	OR (Desc1 LIKE @search OR @search IS NULL)
	OR (Desc2 LIKE @search OR @search IS NULL)
	OR (PG.Desc3 LIKE @search OR @search IS NULL)
	OR (PG.Desc4 LIKE @search OR @search IS NULL)
	OR (PG.Colour LIKE @search OR @search IS NULL))	
	)
SELECT	
ID, 
Brand, 
Name,
ProductCode, 
Category1, 
Category2, 
Category3, 
Category4, 
Desc1, 
Trade, 
TGI, 
SSP,
RowNum
FROM	JBCTE 
WHERE   RowNum between @offset and @lastrecord
Order by  Name asc, Category1 asc, Category2 asc, Category3 asc, Category4 asc
END
If @brand IS NULL AND @showall = 'Y'
BEGIN
	;WITH JBCTE
	AS(
	SELECT 
	PG.ID, 
	Brand, 
	Replace(B.Name,'|', ',') Name,
	Replace(ProductCode,'|', ',') ProductCode, 
	Replace(Category1,'|', ',') Category1, 
	Replace(Category2,'|', ',') Category2, 
	Replace(Category3,'|', ',') Category3, 
	Replace(Category4,'|', ',') Category4, 
	Replace(Desc1,'|', ',') Desc1, 
	Trade, 
	TGI, 
	SSP,
	ROW_NUMBER() OVER (ORDER BY [PG.ID] desc) AS RowNum
	FROM dbo.PriceGuide PG inner join dbo.Brand B 
	on PG.Brand = B.ID 
	WHERE Market = @market
	AND (Category1 like '%'+ @cat1 + '%' OR @cat1 IS NULL)
	AND (Category2 like '%'+ @cat2 + '%' OR @cat2 IS NULL)
	AND (Category3 like '%'+ @cat3 + '%' OR @cat3 IS NULL)
	AND ((B.Name LIKE @search OR @search IS NULL)
	OR (Category1 LIKE @search OR @search IS NULL) 
	OR (Category2 LIKE @search OR @search IS NULL)
	OR (Category3 LIKE @search OR @search IS NULL)
	OR (ProductCode LIKE @search OR @search IS NULL)
	OR (Desc1 LIKE @search OR @search IS NULL)
	OR (Desc2 LIKE @search OR @search IS NULL)
	OR (PG.Desc3 LIKE @search OR @search IS NULL)
	OR (PG.Desc4 LIKE @search OR @search IS NULL)
	OR (PG.Colour LIKE @search OR @search IS NULL))
	)
SELECT	
ID, 
Brand, 
Name,
ProductCode, 
Category1, 
Category2, 
Category3, 
Category4, 
Desc1, 
Trade, 
TGI, 
SSP,
RowNum
FROM	JBCTE 
WHERE   RowNum between @offset and @lastrecord
Order by  Name asc, Category1 asc, Category2 asc, Category3 asc, Category4 asc
END
If @brand IS NULL AND @showall <> 'Y'
BEGIN
	;WITH JBCTE
	AS(
	SELECT 
	PG.ID, 
	Brand, 
	Replace(B.Name,'|', ',') Name,
	Replace(ProductCode,'|', ',') ProductCode, 
	Replace(Category1,'|', ',') Category1, 
	Replace(Category2,'|', ',') Category2, 
	Replace(Category3,'|', ',') Category3, 
	Replace(Category4,'|', ',') Category4, 
	Replace(Desc1,'|', ',') Desc1, 
	Trade, 
	TGI, 
	SSP,
	ROW_NUMBER() OVER (ORDER BY [PG.ID] desc) AS RowNum
	FROM dbo.PriceGuide PG inner join dbo.Brand B 
	on PG.Brand = B.ID 
	WHERE Market = @market
	AND Brand in (Select Brand from dbo.PriceGuidePartnerSuppliers WHERE PartnerID = @partner)
	AND (Category1 like '%'+ @cat1 + '%' OR @cat1 IS NULL)
	AND (Category2 like '%'+ @cat2 + '%' OR @cat2 IS NULL)
	AND (Category3 like '%'+ @cat3 + '%' OR @cat3 IS NULL)
	AND ((B.Name LIKE @search OR @search IS NULL)
	OR (Category1 LIKE @search OR @search IS NULL) 
	OR (Category2 LIKE @search OR @search IS NULL)
	OR (Category3 LIKE @search OR @search IS NULL)
	OR (ProductCode LIKE @search OR @search IS NULL)
	OR (Desc1 LIKE @search OR @search IS NULL)
	OR (Desc2 LIKE @search OR @search IS NULL)
	OR (PG.Desc3 LIKE @search OR @search IS NULL)
	OR (PG.Desc4 LIKE @search OR @search IS NULL)
	OR (PG.Colour LIKE @search OR @search IS NULL))
	)
SELECT	
ID, 
Brand, 
Name,
ProductCode, 
Category1, 
Category2, 
Category3, 
Category4, 
Desc1, 
Trade, 
TGI, 
SSP,
RowNum
FROM	JBCTE 
WHERE   RowNum between @offset and @lastrecord
Order by  Name asc, Category1 asc, Category2 asc, Category3 asc, Category4 asc
END
END
END
GO

Open in new window


Nothing highlighted, but when I try to compile i get --

Msg 207, Level 16, State 1, Procedure PriceGuideSearch2, Line 42
Invalid column name 'PG.ID'

I'm guessing that relates to -

ROW_NUMBER() OVER (ORDER BY [PG.ID] desc) AS RowNum

Open in new window


If I change it to -

ROW_NUMBER() OVER (ORDER BY [ID] desc) AS RowNum

Open in new window


I get

Msg 209, Level 16, State 1, Procedure PriceGuideSearch2, Line 42
Ambiguous column name 'ID'.
0
Koen Van WielinkBusiness Intelligence SpecialistCommented:
Hi Gareth,

The difference between a CTE and a temp table is that a temp table really behaves like any normal database table, so the limitations you have on a CTE (no IF's, can only use it in the part of the script immediately following the CTE, etc) do not apply. The drawback is that the "create table" command is slightly more cumbersome than the CTE because you have to specify the column datatypes. But once that's done, it's far more flexible because you can just insert data into the table like any other and manipulate it more freely than you can on a CTE.
The temp table has a # in front of the name, which means that only the connection that created it can use the table, so other connections on the database cannot access the table. This is what you want as it's just a temporary collection of data which will be discarded after the final select statement has been executed. The drop table statement at the end is just to clean things up. I believe the temp table is automatically dropped when the connection to the database is closed.

Have you tried running the script I gave? I did not get any errors when I parsed it. Of course I can't actually run it as I don't have your dataset. The advantage of the temp table over your latest rewrite is that you only need a single select statement for the final result. Far easier to work with if lateron you have to add/remove details from the result.
0
jogosCommented:
I did not compare every part of your select but on first site you can combint that in a single query.  Just see your where is covering your 3 if-branches

Something like this
	WHERE Market = @market
	AND (	Brand = @brand  -- If @brand IS NOT NULL
                    OR  (@Brand is null -- If @brand IS NULL
                              AND  ( @showall = 'Y'
                                         OR  Brand in (Select Brand 
                                                          from dbo.PriceGuidePartnerSuppliers 
                                                          WHERE PartnerID = @partner)
                                        )

Open in new window



If your complex query is tripled here and changes will be needed in future chances are verry high that somewhere one of the 3 will end up behaving different. General rule if you do same thing different times think if it realy cant be done in a single move. The more comples the more reason for that.
The alternative is to have the difference handled first on a simple compreheseble and maintainable way and then link it once with the more complex part. This is the part where you for example fill a #temptable or a table variable with the Brands you want to select (with the 3 if's) and 'inner join' that as a filter the rest of the query ... here in a CTE.

And as a variation on a CTE you also can define a view with the inner content of your CTE.
But performance can vary from one solution to the other depending on your tuning and how selective your filter is
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
garethtnashAuthor Commented:
Thank you both - great :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.