在SQL Server存储过程中基于不同选择显示结果

debugcn 发表于 Dev

SKJ

我目前的Microsoft SQL Server上的工作2008年我的问题是：我已经通过参数来表示5个条件@rep，@name，@work，@year，@seg。如果我只填写某些条件，该如何编写查询以显示结果？然后我有一个查询，该查询将计算访问的总数。例如，

DECLARE @Rep varchar(100)
DECLARE @Name varchar(100)
DECLARE @Work varchar (100)
DECLARE @Year int
DECLARE @Seg int

SET @RepCode = ''
SET @Name = 'SYED'
SET @Work = ''
SET @Year = '2014'
SET @Seg = ''

我希望结果显示syed2014年的结果以及代表，工作和他的段。我尝试了一下，但没有成功。

if @Rep IS NOT NULL
    SELECT Id, Name, Work, YEAR, Rep 
    FROM #SalesRepVisit
    WHERE Rep = @Rep
ELSE 
   if @Name IS NOT NULL
      SELECT Id, Name, Work, YEAR, Rep 
      FROM #SalesRepVisit
      WHERE Name = @Name
   ELSE
      if @Work IS NOT NULL
         SELECT Id, Name, Work, YEAR, Rep 
         FROM #SalesRepVisit
         WHERE Work = @Work
      ELSE
          if @Year IS NOT NULL
             SELECT Id, Name, Work, YEAR, Rep
             FROM #SalesRepVisit
             WHERE YEAR = @Year
          ELSE
             if @Seg IS NOT NULL
                SELECT Id, Name, Work, YEAR, Rep 
                FROM #SalesRepVisit
                WHERE Seg = @Seg

要计算访问的总数，我有此查询。

SELECT DISTINCT
    Id as Id,
    Name as Customername,
    Rep As Repcode,
    (Select COUNT(*)  from #SalesRepVisit where Month = 1 and Rep = @Rep and Name = @Name AND Work = @Work and YEAR = @Year)as 'January',
    (Select COUNT(*)  from #SalesRepVisit where Month = 2 and Rep = @Rep and Name = @Name AND Work = @Work and YEAR = @Year)as 'February',
    (Select COUNT(*)  from #SalesRepVisit where Month = 3 and Rep = @Rep and Name = @Name AND Work = @Work and YEAR = @Year)as 'March' ,
    (Select COUNT(*)  from #SalesRepVisit where Month = 4 and Rep = @Rep and Name = @Name AND Work = @Work and YEAR = @Year)as 'April',
    (Select COUNT(*)  from #SalesRepVisit where Month = 5 and Rep = @Rep and Name = @Name AND Work = @Work and YEAR = @Year)as 'May',
    (Select COUNT(*)  from #SalesRepVisit where Month = 6 and Rep = @Rep and Name = @Name AND Work = @Work and YEAR = @Year)as 'June',
    (Select COUNT(*)  from #SalesRepVisit where Month = 7 and Rep = @Rep and Name = @Name AND Work = @Work and YEAR = @Year)as 'July',
    (Select COUNT(*)  from #SalesRepVisit where Month = 8 and Rep = @Rep and Name = @Name AND Work = @Work and YEAR = @Year)as 'August',
    (Select COUNT(*)  from #SalesRepVisit where Month = 9 and Rep = @Rep and Name = @Name AND Work = @Work and YEAR = @Year)as 'September',
    (Select COUNT(*)  from #SalesRepVisit where Month = 10 and Rep = @Rep and Name = @Name AND Work = @Work and YEAR = @Year)as 'October',
    (Select COUNT(*)  from #SalesRepVisit where Month = 11 and Rep = @Rep and Name = @Name AND Work = @Work and YEAR = @Year)as 'November',
    (Select COUNT(*)  from #SalesRepVisit where Month = 12 and Rep = @Rep and Name = @Name AND Work = @Work and YEAR = @Year)as 'December',
    Work as Work,
    YEAR as Year
FROM #SalesRepVisit
WHERE 
    Rep = @Rep and Name = @Name  AND Work = @Work AND YEAR = @Year

查询不显示任何内容。我希望查询显示“ syed”的结果以及他的名字，代表，工作，段和年。

拉杰什

@Radar的改进答案

在这种Where情况下我们可以使用Case语句

SELECT Id,
       Name,
       SUM ( CASE WHEN Month =1 THEN 1 ELSE 0 END ) as 'January',
       SUM ( CASE WHEN Month =2 THEN 1 ELSE 0 END ) as 'February',
       ...
       ...
FROM   #SalesRepVisit
where  ( Rep  = Case When IsNull(@RepCode,'') ='' then Rep else @RepCode end)
   AND ( Name = Case When IsNull(@Name,'')='' then Name else @Name end)
   AND ( Work = Case When IsNull(@Work,'') ='' then Work else @Work end)
   AND ( YEAR = Case When IsNull(@Year,'') ='' then YEAR else @Year end)
GROUP BY Id, Name

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。