Server存储过程仅返回一个值

Question

当返回一个存储过程时，我有一些问题，我需要所有的选择来创建一个图表。

这是我的程序：

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[LastWeekQuotesByAssociate]
AS
BEGIN
    SELECT 
        networkid, COUNT(isQuoteComplete) as Total 
    FROM
        email 
    WHERE
        (networkID = 'crbark00' 
        AND emailDateEntry > (DATEADD(WEEK, -1, GETDATE()))) 
    GROUP BY
        networkID

    SELECT 
        networkid, COUNT(isQuoteComplete) as Total 
    FROM
        email 
    WHERE
        (networkID = 'crherb00' 
        AND emailDateEntry > (DATEADD(WEEK, -1, GETDATE()))) 
    GROUP BY
        networkID

    SELECT 
        networkid,
        COUNT(isQuoteComplete) as Total 
    FROM
        email 
    WHERE
        (networkID = 'crcalr25'
        AND emailDateEntry > (DATEADD(WEEK, -1, GETDATE()))) 
    GROUP BY
        networkID

   SELECT networkid,COUNT(isQuoteComplete) as Total from email where(networkID = 'craran00'and emailDateEntry > (DATEADD(WEEK, -1, GETDATE()))) group by networkID
SELECT networkid,COUNT(isQuoteComplete) as Total from email where(networkID = 'crcerg25'and emailDateEntry > (DATEADD(WEEK, -1, GETDATE()))) group by networkID
SELECT networkid,COUNT(isQuoteComplete) as Total from email where(networkID = 'crdelj00'and emailDateEntry > (DATEADD(WEEK, -1, GETDATE()))) group by networkID
SELECT networkid,COUNT(isQuoteComplete) as Total from email where(networkID = 'crromj00'and emailDateEntry > (DATEADD(WEEK, -1, GETDATE()))) group by networkID
SELECT networkid,COUNT(isQuoteComplete) as Total from email where(networkID = 'crarrl00'and emailDateEntry > (DATEADD(WEEK, -1, GETDATE()))) group by networkID
SELECT networkid,COUNT(isQuoteComplete) as Total from email where(networkID = 'crverj00'and emailDateEntry > (DATEADD(WEEK, -1, GETDATE()))) group by networkID
SELECT networkid,COUNT(isQuoteComplete) as Total from email where(networkID = 'crmore00'and emailDateEntry > (DATEADD(WEEK, -1, GETDATE()))) group by networkID
SELECT networkid,COUNT(isQuoteComplete) as Total from email where(networkID = 'crgomf00'and emailDateEntry > (DATEADD(WEEK, -1, GETDATE()))) group by networkID
SELECT networkid,COUNT(isQuoteComplete) as Total from email where(networkID = 'crvarm00'and emailDateEntry > (DATEADD(WEEK, -1, GETDATE()))) group by networkID
SELECT networkid,COUNT(isQuoteComplete) as Total from email where(networkID = 'crmong00'and emailDateEntry > (DATEADD(WEEK, -1, GETDATE()))) group by networkID
SELECT networkid,COUNT(isQuoteComplete) as Total from email where(networkID = 'crarim00'and emailDateEntry > (DATEADD(WEEK, -1, GETDATE()))) group by networkID
SELECT networkid,COUNT(isQuoteComplete) as Total from email where(networkID = 'crjimj00'and emailDateEntry > (DATEADD(WEEK, -1, GETDATE()))) group by networkID
SELECT networkid,COUNT(isQuoteComplete) as Total from email where(networkID = 'crvilg00'and emailDateEntry > (DATEADD(WEEK, -1, GETDATE()))) group by networkID 
END

这是在执行时：

USE [Quotes]
GO

DECLARE @return_value int

EXEC    @return_value = [dbo].[LastWeekQuotesByAssociate]

SELECT  'Return Value' = @return_value
GO

它只返回优先选择！

任何暗示，都有人能帮我！

谢谢

Answer 1

如果您这样做，您需要添加

UNION ALL

在每一个选择之间。

SELECT networkid,COUNT(isQuoteComplete) as Total from email where(networkID = 'crbark00'and emailDateEntry > (DATEADD(WEEK, -1, GETDATE()))) group by networkID
UNION ALL
SELECT networkid,COUNT(isQuoteComplete) as Total from email where(networkID = 'crherb00'and emailDateEntry > (DATEADD(WEEK, -1, GETDATE()))) group by networkID

更好的解决方案是生成包含networkIds的列表/表，然后使用'in‘类：

SELECT networkid,COUNT(isQuoteComplete) as Total 
from email 
where 
    networkID in ('crbark00', 'crbark01', 'crbark02') 
    and emailDateEntry > DATEADD(WEEK, -1, GETDATE()) 
group by networkID

也许可以使用子subselect代替列出每个networkId

Answer 2

 SELECT networkid,COUNT(isQuoteComplete) as Total from email 
      where networkID  in ('crbark00','crxxxxx') 
      and emailDateEntry > (DATEADD(WEEK, -1, GETDATE()))) group by networkID

Answer 3

当每个SELECT语句完成后，请使用UNION ALL命令，例如选择networkid，计数(IsQuoteComplete)作为来自电子邮件的总计(networkID = 'crbark00‘和emailDateEntry >)，networkID UNION组(WEEK，-1，GETDATE())-所有选择网络，计数(IsQuoteComplete)从电子邮件中选择networkID= 'crherb00’和emailDateEntry > (networkID =‘crherb00’和emailDateEntry>)-1，GETDATE(())组，由networkID