Filter rows in stored procedure SQL Server

Question

I would like to filter rows in my stored procedure, so when I search for a Channel, I get only the channel value I've specified in a parameter field.

Here is the code:

CREATE TABLE iGuide 
    ([Zone] varchar(3), [Enabled] int, [Channel] varchar(4), [DMA] varchar(7), [Region] varchar(7), [HQ] varchar(10), [Machine] varchar(10))
;

INSERT INTO iGuide 
    ([Zone], [Enabled], [Channel], [DMA], [Region], [HQ], [Machine])
VALUES
    ('ACC', 1, 'AEN', 'Norfolk', 'Other', 'virginia', 'dzsfk'),
    ('ACC', 1, 'CNN', 'Norfolk', 'Other', 'virginia', 'dzsfk'),
    ('ACC', 1, 'ESPN', 'Norfolk', 'Other', 'virginia', 'dzsfk'),
    ('ACC', 1, 'HIST', 'Norfolk', 'Other', 'virginia', 'dzsfk'),
    ('ACC', 1, 'FOOD', 'Norfolk', 'Other', 'virginia', 'dzsfk'),
    ('ANJ', 1, 'AEN', 'Paducah', 'Pacific', 'hqs3ftbgwi', 'adanj'),
    ('ANJ', 1, 'ESPN', 'Paducah', 'Pacific', 'hqs3ftbgwi', 'adanj'),
    ('ANJ', 1, 'HIST', 'Paducah', 'Pacific', 'hqs3ftbgwi', 'adanj'),
    ('ANJ', 1, 'CNN', 'Paducah', 'Pacific', 'hqs3ftbgwi', 'adanj'),
    ('CHD', 1, 'ESPN', 'Denver', 'Pacific', 'hqs3ftbgwi', 'adchd'),
    ('CHD', 1, 'FOOD', 'Denver', 'Pacific', 'hqs3ftbgwi', 'adchd'),
    ('CHD', 1, 'DISC', 'Denver', 'Pacific', 'hqs3ftbgwi', 'adchd'),
    ('CHI', 1, 'AEN', 'Denver', 'Pacific', 'hqs3ftbgwi', 'adchd'),
    ('CHI', 1, 'FOOD', 'Chico', 'Pacific', 'hqs4ftbgwi', 'adv1chicca'),
    ('CHI', 1, 'ESPN', 'Chico', 'Pacific', 'hqs4ftbgwi', 'adv1chicca'),
    ('CHI', 1, 'CNN', 'Chico', 'Pacific', 'hqs4ftbgwi', 'adv1chicca')
;

GO

CREATE Procedure [dbo].[Usp_GetWHERE]
    @Filter Varchar(MAX)

AS

   DECLARE @columns NVARCHAR(MAX)      
           ,@sql NVARCHAR(MAX)
           ,@zone NVARCHAR(MAX)


    SET @columns = N''
    --Get column names for entire pivoting
    SELECT @columns += N', ' + QUOTENAME(Zone)
      FROM (select distinct Zone 
            from iGuide  WHERE (DMA LIKE @Filter)   OR (Channel = @Filter) OR (@Filter = ' Select All') OR (@Filter = 'Select All') OR (Zone LIKE @Filter) OR (Machine LIKE @Filter) OR (HQ LIKE @Filter) OR (Region LIKE @Filter)
         ) AS T

    select @columns;

    SET @zone = N''
    --Get column names for entire pivoting
    SELECT @zone += N', ' + QUOTENAME(Zone, '''')
      FROM (select distinct Zone 
            from iGuide  WHERE (DMA LIKE @Filter)   OR (Channel = @Filter) OR (@Filter = ' Select All') OR (@Filter = 'Select All') OR (Zone LIKE @Filter) OR (Machine LIKE @Filter) OR (HQ LIKE @Filter) OR (Region LIKE @Filter)
         ) AS T

    select  @zone;    

    SET @zone = '(' + substring(@zone, 3, len(@zone)-2) + ')';         
    select  @zone;


    SET @sql = N'
    SELECT  [Channel], ' + STUFF(@columns, 1, 2, '') + '
    FROM (SELECT Channel, [Zone]
          FROM iGuide 
          WHERE [Zone] IN ' + @zone + ')  as source
    PIVOT
    (
      count([Zone]) FOR [Zone] IN ('
      + STUFF(REPLACE(@columns, ', [', ',['), 1, 1, '')
      + ')
    ) AS Pivot1  ORDER BY Channel
    ' 
    select @sql;
    EXEC sp_executesql @sql;

GO 
EXECUTE [dbo].Usp_GetWHERE N'AEN';      

RESULT OF THIS CODE:

    Channel ACC ANJ CHI
1   AEN      1   1   1
2   CNN      1   1   1
3   ESPN     1   1   1
4   FOOD     1   0   1
5   HIST     1   1   0

My desired result when executing 'AEN' as a value should be:

    Channel ACC ANJ CHI
1   AEN      1   1   1

Answer 1

You are getting 5 results because @filter might be matching with DMA or Channel or Zone or Machine or HQ or Region.

If you want to filter strictly on the basis of Channel then either change @zone to

 SELECT @zone += N', ' + QUOTENAME(Zone, '''')
      FROM (select distinct Zone 
            from iGuide  WHERE Channel = @Filter
         ) AS T

or filter it in the main query by adding a condition for channel=@Filter

SET @sql = N'
SELECT  [Channel], ' + STUFF(@columns, 1, 2, '') + '
FROM (SELECT Channel, [Zone]
      FROM iGuide 
      WHERE [Zone] IN ' + @zone + ' AND (([Channel] LIKE ''' + @Filter +''') OR ([Zone] LIKE ''' + @Filter +''') OR ([DMA] LIKE ''' + @Filter +''') OR ([HQ] LIKE ''' + @Filter +''') OR ([Machine] LIKE ''' + @Filter +''') OR ([Region] LIKE ''' + @Filter +'''))) as source
    PIVOT
    (
      count([Zone]) FOR [Zone] IN ('
      + STUFF(REPLACE(@columns, ', [', ',['), 1, 1, '')
      + ')
    ) AS Pivot1  ORDER BY Channel
    ' 

Answer 2

All you need to do is add it into the WHERE

SET @sql = N'
SELECT  [Channel], ' + STUFF(@columns, 1, 2, '') + '
FROM (SELECT Channel, [Zone]
      FROM iGuide 
      WHERE [Zone] IN ' + @zone + ' AND [Channel] = @Your_Param_Here)  as source

Answer 3

Added a where clause to your pivot.

ALTER Procedure [dbo].[Usp_GetWHERE]
    @Filter Varchar(MAX)

AS

   DECLARE @columns NVARCHAR(MAX)      
           ,@sql NVARCHAR(MAX)
           ,@zone NVARCHAR(MAX)


    SET @columns = N''
    --Get column names for entire pivoting
    SELECT @columns += N', ' + QUOTENAME(Zone)
      FROM (select distinct Zone 
            from iGuide  WHERE (DMA LIKE @Filter)   OR (Channel = @Filter) OR (@Filter = ' Select All') OR (@Filter = 'Select All') OR (Zone LIKE @Filter) OR (Machine LIKE @Filter) OR (HQ LIKE @Filter) OR (Region LIKE @Filter)
         ) AS T

    select @columns;

    SET @zone = N''
    --Get column names for entire pivoting
    SELECT @zone += N', ' + QUOTENAME(Zone, '''')
      FROM (select distinct Zone 
            from iGuide  WHERE (DMA LIKE @Filter)   OR (Channel = @Filter) OR (@Filter = ' Select All') OR (@Filter = 'Select All') OR (Zone LIKE @Filter) OR (Machine LIKE @Filter) OR (HQ LIKE @Filter) OR (Region LIKE @Filter)
         ) AS T

    select  @zone;    

    SET @zone = '(' + substring(@zone, 3, len(@zone)-2) + ')';         
    select  @zone;


    SET @sql = N'
    SELECT  [Channel], ' + STUFF(@columns, 1, 2, '') + '
    FROM (SELECT Channel, [Zone]
          FROM iGuide 
          WHERE [Zone] IN ' + @zone + ')  as source
    PIVOT
    (
      count([Zone]) FOR [Zone] IN ('
      + STUFF(REPLACE(@columns, ', [', ',['), 1, 1, '')
      + ')
    ) AS Pivot1
    WHERE channel = ''' + @filter + ''' ORDER BY Channel
    ' 
    select @sql;
    EXEC sp_executesql @sql;

GO 
EXECUTE [dbo].Usp_GetWHERE N'AEN';