CODEWITHSUNDEEP
sqlsql-server-2008stored-procedures
Beginer
Beginer

Reputation: 240

Stored procedure: if no record exists run different select

In my stored procedure, I have the following statement:

SELECT TOP 200 
    C.CustomerNumber,
    C.CustomerName,
    C.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 ('%' +  @searchString + '%') 
    OR C.CustomerNumber LIKE ('%' + CONVERT(VARCHAR(61), @searchString) + '%')))
ORDER BY
    C.CustomerName

I want to check now that if this select returns no records, run a different select.

Upvotes: 0

Views: 346

Answers (2)

jyao
jyao

Reputation: 1630

I would prefer this way

if exists (
    SELECT 1
    FROM 
        Customer C WITH(NOLOCK, index (UQ_Customer_CustomerNumber))
    LEFT JOIN 
        CustomerQuotingPreference cp WITH(NOLOCK) ON cp.CustomerId = C.CustomerId
    WHERE  
        (C.CustomerName LIKE ('%' +  @searchString + '%') 
        OR C.CustomerNumber LIKE ('%' + CONVERT(VARCHAR(61), @searchString) + '%')))
)

SELECT TOP 200  
    C.CustomerNumber,
    C.CustomerName,
    C.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 ('%' +  @searchString + '%') 
    OR C.CustomerNumber LIKE ('%' + CONVERT(VARCHAR(61), @searchString) + '%')))
ORDER BY
    C.CustomerName;
else
    SELECT * FROM OtherTable;

Upvotes: 0

John Wu
John Wu

Reputation: 52210

DECLARE @Temp Table
(
    CustomerNumber VarChar, //or whatever type these are
    CustomerName   VarChar,
    AccountManager VarChar,
    CustomerId     Int
)

INSERT INTO @Temp
SELECT TOP 200  
    C.CustomerNumber,
    C.CustomerName,
    C.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 ('%' +  @searchString + '%') 
    OR C.CustomerNumber LIKE ('%' + CONVERT(VARCHAR(61), @searchString) + '%')))
ORDER BY
    C.CustomerName

IF @@ROWCOUNT <> 0
    BEGIN
        SELECT * FROM @Temp
        RETURN
    END

SELECT * FROM OtherTable

Upvotes: 1

Related Questions