Correct use of RowCount

Question

I have the following in my stored procedure:

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

When I run this, I get results for both the selects instead of last one:

How can I get results from just one of the select depending on the condition met?

Answer 1

There are definitely many different ways to do this. One way just that comes to mind is you could drop the results from the first select into a table variable or temp table. Then do an If/Else based on the row count being greater than 0 on that or an if exists select 1 from temp table type of check, and have only one select execute in the end based on that if/else check, either select off that temp table/variable or select off option B.

Answer 2

How about...

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