筛选存储过程 SQL Server 中的行

Question

我想在存储过程中筛选行，因此当我搜索通道时，我只得到我在参数字段中指定的通道值。

这是代码：

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';      

此代码的结果：

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

执行"AEN"作为值时，我想要的结果应该是：

Channel ACC ANJ CHI
1   AEN      1   1   1

Answer 1

在透视中添加了一个 where 子句。

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';   

Answer 2

您得到 5 个结果，因为@filter可能与DMA或Channel或Zone或Machine或HQ或Region匹配。

如果要严格根据通道进行过滤，请将@zone更改为

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

或通过添加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 3

您需要做的就是将其添加到 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