正确使用RowCount

Question

我的存储过程中有以下内容：

DECLARE @CustomerNumber nvarchar(50)

SELECT TOP 200 
    C.CustomerNumber as SourceSystemId,
    C.CustomerName,
    AccountManager,
    C.CustomerId        
FROM 
    Customer C WITH(NOLOCK, index (UQ_Customer_CustomerNumber))
LEFT JOIN 
    CustomerQuotingPreference cp WITH(NOLOCK)ON cp.CustomerId = C.CustomerId
WHERE  
    (C.CustomerName LIKE ('%1725614%') 
    OR C.CustomerNumber LIKE ('%' + CONVERT(VARCHAR(61), '1725614' ) +'%'))         
ORDER BY
    C.CustomerName

IF @@ROWCOUNT = 0  
BEGIN 
    SELECT DISTINCT 
        @CustomerNumber= C.CustomerNumber
    FROM
        [IFO].dbo.PricedealProposal PP
    JOIN
        IFO.dbo.OrderQuote OQ ON PP.OrderQuoteId = OQ.OrderQuoteId      
    JOIN
        Customer C ON C.CustomerId = OQ.CompanyId
    WHERE
        PP.PriceDealProposalId = '1725614' 

    EXEC [dbo].[Customer_ListCustomerDetailsByCustomerNumber] @searchString = @CustomerNumber   
END

当我运行这段代码时，我得到了两次选择的结果，而不是最后一次：

​

​

根据满足的条件，如何仅从一个select中获得结果？

Answer 1

当然，有很多不同的方法可以做到这一点。我想到的一种方法是，您可以将第一次select的结果放到一个表变量或临时表中。然后，基于行数大于0执行If/Else，或者if exists从temp table类型的检查中选择1，并根据if/else检查在最后只执行一次select，或者选择off该临时表/变量，或者选择off选项B。

Answer 2

不如..。

DECLARE @CustomerNumber nvarchar(50)
IF NOT EXISTS(
        SELECT TOP 200 C.CustomerNumber as SourceSystemId
            ,C.CustomerName             
            ,AccountManager
            ,C.CustomerId       
        FROM Customer C WITH(NOLOCK, index (UQ_Customer_CustomerNumber))
        LEFT JOIN CustomerQuotingPreference cp WITH(NOLOCK)ON cp.CustomerId = C.CustomerId
        WHERE  (C.CustomerName like ('%1725614%') 
            OR C.CustomerNumber like ('%' + CONVERT(VARCHAR(61),'1725614' ) +'%'))
)
        BEGIN 
            --NOTE TOP 1 HERE!
            select TOP 1 @CustomerNumber= C.CustomerNumber
            from [IFO].dbo.PricedealProposal PP
            join IFO.dbo.OrderQuote OQ
            on PP.OrderQuoteId = OQ.OrderQuoteId        
            join Customer C
            on C.CustomerId= OQ.CompanyId
            Where PP.PriceDealProposalId = '1725614' 

            EXEC [dbo].[Customer_ListCustomerDetailsByCustomerNumber] @searchString = @CustomerNumber   
        END
        ELSE
        BEGIN
            SELECT TOP 200 C.CustomerNumber as SourceSystemId
                ,C.CustomerName             
                ,AccountManager
                ,C.CustomerId       
            FROM Customer C WITH(NOLOCK, index (UQ_Customer_CustomerNumber))
            LEFT JOIN CustomerQuotingPreference cp WITH(NOLOCK)ON cp.CustomerId = C.CustomerId
            WHERE  (C.CustomerName like ('%1725614%') 
                OR C.CustomerNumber like ('%' + CONVERT(VARCHAR(61),'1725614' ) +'%'))
        END